Access Denied - Remote connection to SSIS

We have 2 SQL2005 servers currently. 1 is a dev box, the other production. To grant remote access to SSIS, I added my developers to the local Distributed COM Users group and also gave them remote Access and remote Activation on the MsDtsServer component.

This worked just fine. Recently we applied SP1 and the follow-on hotfixes to all SQL components. I followed the install order prescribed in the Q article and applied all component fixes. My developers are now unable to access SSIS remotely unless I add them to the local administrator's group. I don't see anywhere that a log gets generated to help me track down what's going on. Nothing shows up in my Windows security log, despite enabling audit logging on: Accout Logon Events, Logon Event, Object Access, and Privilege Use.

Is this a know issue in SP1 or the hotfixes? I'm hesitant to apply the patches to my production box until I can resolve this issue.

Thanks
Steve

With the lack of responses I'm assuming I'm out of luck. Anyone have suggestions on how to troubleshoot this? Is there some reg entry I can flip to enable debugging or even use of the event log for SSIS?

Thanks

|||When you say you have added developers to DCOM Users Groups, is this what you have done - Connecting to a Remote Integration Services Server (17th July 2006) - http://msdn2.microsoft.com/en-us/library/aa337083.aspx ?|||

That article only mentions the need to give them access to the msdtsserver. There is another article that also mentions that they must be placed in the local Windows group called: "Distributed COM Users". I have followed both articles. As I mentioned, this configuration was working until a short time after applying the SP1 and Hotfix patches for SQL2005. It is still working on our production box that has not yet been patched.

Thanks

Steve

|||

I am getting the same behaviour, we have also got SP1 installed.

|||

After applying SP1 and the Hotfixes, we never rebooted Windows (never received a notification that we should). This weekend we applied our monthly round of OS patches. Now that the server has been rebooted access is working correctly again.

I guess the lesson here is that, if you've followed all of the instructions and they still don't work, do a full reboot - restarting services doesn't appear to be enough.

You may consider this thread closed.

Thanks

Steve

access data in remote SQL server with trusted authority

I have two win2003 servers. One is IIS and the another is SQL server. I
can use MyConnection=new
SqlConnection("server=SQLServerName;database=myDBName;UID=sa;PWD= mypwd")
to access my data. But I can not use MyConnection=new
SqlConnection("server=SQLServerName;database=myDBName;Integrated
Security=SSPI") to access it. If I put both IIS and SQL Server in one
machine, I can do anything. How can I do something to allow trusted
authority work ?

ThanksYou might find these KB articles (or one of the ones they link to)
useful:

http://support.microsoft.com/defaul...kb;en-us;316989
http://support.microsoft.com/defaul...kb;en-us;247931

If these don't help, I suggest you clarify what you mean by "can not
use" - do you get a specific error message?

Simonsql

Access a in house sql server from a remote web server

does anybody know how I would create a connection string in asp.net 2.0express to access a database in a different location from the webserver. I have a static IP address on the web server, so I've allowedaccess from the web server through the firewall. I have a user name andpassword, and I've made sure that I have the correct database. Would Iaccess the database using the network IP address and if so, where do Iput it. Thank you to anyone that can give me some insight to thisproblem, I'm very new to asp.net so, about a weeks worth of knowledge,so if you could explain as such I would appreciate it.
Seewww.connectionstrings.com. You put the IP where you normally put the Server name, all else should be the same. That said, hitting a SQL Server on the other side of a firewall may not be the best thing to do.|||thanks
"That said, hitting a SQL Server on the other side of a firewall may not be the best thing to do."
what you recommend then, like I said, I'm pretty new to this. Theproject that I'm working on requires me to create a web form, that ison an outsourced web server, and import the data into our database thatis behind a firewall. Would this be a security issue?

|||Yes, it can be. If SQL Server was perfect, it would not be a problem to leave the SQL port open. SQL Server is not perfect (recall the Slammer virus). So, in the circumstances you describe, perhaps it is your only option. However, I would make very certain that the database server is always patched up to date.sql

Accesing the SQL Server 2005

Hi,

I have a asp.net 2.0 web applicaiton which is connecting to the remote SQL server 2005. When I run the Web app, then Iam connecting to the DB and its displaying the records on the page.

When I browse the same page from the IIS, then iam getting the following error:

"System.Data.SqlClient.SqlException: Login failed for user 'mydomain\user' "

Connection string :

connectionString="Data Source=MySystem;Initial Catalog=DBLive;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>

Please let me know if find accross a solution for this...

Thanks

Vaskam

Verify the 'user' assigned to the Web Servers' AppDomain.

Somewhere, you seem to have assigned the security to 'mydomain\user' -I hope that your domain is NOT called 'mydomain' and that you do NOT have a user named 'user'.

About trace flag 8588

Hi Guys,
I got an error about trace flag 8588, Error 8526 Level 16: Cannot go remote
while the session is enlisted in a distributed transaction that has an active
savepoint. I setup two sql server 2000 in two windows 2003 server, both
install sql server sp1. I created linked server and setup MCDTC on both
server. When I run application that include distributed transaction, I got
error above. I re-check MCDTC, permission and so on, but I still got that
issue. Could some one help me to fix it? Thanks in advance.For more information about using trace flags and startup parameters, see the
"DBCC TRACEON" and "Using Startup Options" topics in SQL Server 2000 Books
Online.
To use trace flag 8599, obtain the latest service pack for SQL Server 2000:
Q290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
Once you update the service pack your problem will get fixed. Please update
the same and lemme know if it works...
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Iter" wrote:
> Hi Guys,
> I got an error about trace flag 8588, Error 8526 Level 16: Cannot go remote
> while the session is enlisted in a distributed transaction that has an active
> savepoint. I setup two sql server 2000 in two windows 2003 server, both
> install sql server sp1. I created linked server and setup MCDTC on both
> server. When I run application that include distributed transaction, I got
> error above. I re-check MCDTC, permission and so on, but I still got that
> issue. Could some one help me to fix it? Thanks in advance.
>|||Thank you very much. But for my case, the server is windows 2003 and have
already installed service pack 1. Is there something else that I need to
install? Thanks.
"Sreejith G" wrote:
> For more information about using trace flags and startup parameters, see the
> "DBCC TRACEON" and "Using Startup Options" topics in SQL Server 2000 Books
> Online.
> To use trace flag 8599, obtain the latest service pack for SQL Server 2000:
> Q290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
> Once you update the service pack your problem will get fixed. Please update
> the same and lemme know if it works...
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Iter" wrote:
> > Hi Guys,
> > I got an error about trace flag 8588, Error 8526 Level 16: Cannot go remote
> > while the session is enlisted in a distributed transaction that has an active
> > savepoint. I setup two sql server 2000 in two windows 2003 server, both
> > install sql server sp1. I created linked server and setup MCDTC on both
> > server. When I run application that include distributed transaction, I got
> > error above. I re-check MCDTC, permission and so on, but I still got that
> > issue. Could some one help me to fix it? Thanks in advance.
> >

About the security

Dear all,
How to indetify if there is a trace running against a sql server or not? I
will try to explain better. I've got lots of sql2k remote servers added into
my EMC client. Up to here that's fine. But due to a big complexity of our
organization sometimes oneself have serious doubts about of the "do and
donts" (you could think that's ridiculous or even amazing but that's the
truth)
If I perfectly know that auditing process are running then I don't want do
any SELECT, obviously.
"exec sp_trace_getdata 2, 0" in current activity and along with 'Sql
Profiler' obvioulsy say us that Sql tool for that is running. But the
question is, if were another tool disguissed doing that?
You never will have the total security of that anybody is spying one server.
thanks in advance,
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)It may be going off at a bit of a tangent, but if you need to be that
sure that no-ones going to capture your SQL statements, have you
considered encrypting the statement, sending that to an SP that will
decrypt it and then run it using sp_executesql. This way the actual
statement should never be seen as it is executed as part of the outer
stored procedure. Only I can't see why if someone really wanted to get
at your statements they couldn't just read the packets going through,
and never actually interface with the SQL server (though I admit I do
not know how SQL server actually sends its instructions - perhaps they
are already encrypted while being sent).|||fn_trace_getinfo
"Enric" wrote:

> Dear all,
> How to indetify if there is a trace running against a sql server or not? I
> will try to explain better. I've got lots of sql2k remote servers added in
to
> my EMC client. Up to here that's fine. But due to a big complexity of our
> organization sometimes oneself have serious doubts about of the "do and
> donts" (you could think that's ridiculous or even amazing but that's the
> truth)
> If I perfectly know that auditing process are running then I don't want do
> any SELECT, obviously.
> "exec sp_trace_getdata 2, 0" in current activity and along with 'Sql
> Profiler' obvioulsy say us that Sql tool for that is running. But the
> question is, if were another tool disguissed doing that?
> You never will have the total security of that anybody is spying one serve
r.
> thanks in advance,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)

About PlatformNotSupportedException in WM 5.0 with SQL Server

Hi All, I am now trying to develop an application connecting to a remote SQL server in a PDA with Windows Mobile 5.0 in Visual Studio.

I have installed SDK for WM5.0 for the development.

But I got the following Exception

System.PlatformNotSupportedException was unhandled
Message="PlatformNotSupportedException"
StackTrace:
at System.Globalization.CultureInfo..ctor()
at System.Globalization.CultureInfo..ctor()
at System.Data.SqlClient.TdsParser.GetCodePage()
at System.Data.SqlClient.TdsParser.ProcessEnvChange()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.SqlInternalConnection.Login()
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor()
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen()
at System.Data.Common.DbDataAdapter.FillInternal()
at System.Data.Common.DbDataAdapter.Fill()
at System.Data.Common.DbDataAdapter.Fill()
at DeviceApplication1.rfidcpsDataSetTableAdapters.VehicleTableAdapter.Fill()
at DeviceApplication1.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at DeviceApplication1.Form1.Main()

I just cannot figure what's the problem. Please help me! Thanks a lot!

That means you're trying to use locale which is not supported on your device (or emulator). With SQL Client it happens if your database has collation which is not available on device.

How to fix it:

1. Get device/emulator which supports collation you're using in your database (best way to go). Usually that would be device localized in particular language.

2. Purchase and install localization package on to your device. Note: some of these packages are do not provide complete localization required by NETCF and might not work, so test before you buy.

3. Change database collation to whatever is supported on your device. This one has consequences of potentially incorrect sorting, etc. Please refer to SQL Server documentation for collation impact.

|||

Thanks. Actually my database is just in English and no other language.

So I go for changing database collation. But in the Option, I don't know which one is supported in my PDA (Dell Axim X51v). In the PDA with WM 5.0, the regional setting is to English(United States). In th SQL Server Database collation, it was set to Chinese_Hong_Kong_Stroke_90_CI_AI_WS.

Can you tell me which collation I should set to as well as the locale setting in the PDA?

Thanks a lots!

|||

If your device is in English then I would guess something like SQL_Latin1_General* in it would do.

|||

I tried to change the database collation to "SQL_Latin1_General_CP1_CI_AI", with PDA setting to English(United States), but the same error came out!

Here comes my code and error

Dim sqlConnection1 As New SqlConnection("Data Source=BILLY\SQLEXPRESS;Initial Catalog=rfidcps;Persist Security Info=True;User ID=*****; Password=****;")

Dim cmd As New SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "SELECT * FROM Vehicle WHERE MainTagID = '123''"

Dim i As Integer = 0

cmd.CommandType = CommandType.Text

cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()

--

ERROR

System.PlatformNotSupportedException was unhandled
Message="PlatformNotSupportedException"
StackTrace:
at System.Globalization.CultureInfo..ctor()
at System.Globalization.CultureInfo..ctor()
at System.Data.SqlClient.TdsParser.GetCodePage()
at System.Data.SqlClient.TdsParser.ProcessMetaData()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at RFIDApp.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at RFIDApp.Form1.Main()

Can any one help me?

|||

Use debugger to see which locale/LCID it's trying to use. Try another collation.

I'm also quite surprised you can even connect to the server - named instances do not work with devices because there's no support for named pipes. Are you running another SQL Server by any chance? Since you obviously can connect I would suspect this connection is made to the wrong server and you're changing collations not on the database you're connecting to. See this for more info on SQL Express connections from NETCF.

|||

"Use debugger to see which locale/LCID it's trying to use."

-> How can I check it?

Actually I did try to use the emulator to run but I failed to run and showed some error. So I stopped to carry on.

Ha, I don't know why, but, I can sure that my computer has only one instance of SQL Server (SQL Server Express Edition). So, the collation setting is the right one I used.

Any other advice? Thanks a lot!

|||

In the Debug/Exceptions... menu check the "Thrown" box for CLR exception. Run to the right exception and look at call stack window, you'll see something like this on the top:

mscorlib.dll!System.Globalization.CultureTableRecord.GetCultureTableRecord(int cultureId = 4556, bool useUserOverride = true) + 0x3c bytes

First number is a culture ID it's trying to get, should be 1033 for ENU. It would be something else in your case, look up which culture is that on MSDN.

Have you tried localized emulator, e.g. in CHT?

Try following the tutorial. Try changing collation for entire SQL Server Express.

|||

Thanks. I've used your method to check the cultureID. The conclusion is that it is set to 0xC04 Chinese (Hong Kong SAR, PRC)

I think it is the problem!! But how can I really change it to other value? How to chang this ID to ENGLISH?

Thanks a lot for your help!

|||

You already know that – you need to change database collation. It’s pretty clear what your attempt to change it has failed and you’re still using Chinese_Hong_Kong_Stroke_90_CI_AI_WS so client if forced to create matching locale (Chinese (Hong Kong SAR, PRC)) which is failing because your device does not support it.

I don’t know why it did not work, I would suggest asking in SQL Express forum. Perhaps SQL Express only allows one collation per server or something…

Also try creating brand new database with some ENU collation, see if that works.

|||

Yes, I finally understand the problem.

But what makes trouble is that I DID reinstall the SQL server express for changing the collation at server-level to Latin1_General_CI_AI,

Also, I DID change the database collation to Latin1_General_CI_AI as well! But how come from the debugger, the locale is Chinese (Hong Kong SAR, PRC)!!!!!!!

I have double-checked the setting in SQL Server Managment Studio Express! I tried to restart my computer as well but there's no change!!

Feel so lost!

|||I'm with Ilya on this one, please post this question in the SQL Server forum|||Thanks all of you. Hope someone could help me. :)

About distributed trans

I have two Server. One is Local, another is Remote. Both Installed SQL Server 2000 at Server 2000.
I start MSDTC on both the local and the remote. The Remote SQL Server start MTS.

When I run the code forward, the error message '7391' will occur

begin distributed tran
select * from rmtdb.pubs.dbo.employee --rmtdb is a linked server
go

How to verify the OLE DB provider specified supports distributed transactions?
How can I do for this error message?
How can I reconfig the Local Server and the Remote?Make sure you have these settings before SP or code :

SET ANSI_DEFAULTS ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON

About building international applications in dotnet

Hi,

I am developing an application using C#.NET that accesses a remote database that that contains japanese characters.

My sample querry is strCommand = "SELECT * FROM table WHERE prodname = 'ＣＲ新海物語スペシャルＭ８'";

The result of querry displayed in the binded datagrid also contains other rows that has this prodname:

ＣＲＦ湯けむり紀行ＳＦ－Ｔ
ＣＲぱちんこハイサイＶＲ１
ＣＲホワイトエンジェルＦＳ

etc.

Thanks in advance for your help.

You forgot to ask a question.
|||

Hi,

I am developing an application using C#.NET that accesses a remote database that that contains japanese characters.

My sample querry is strCommand = "SELECT * FROM table WHERE prodname = 'ＣＲ新海物語スペシャルＭ８'";

The result of the querry above that is displayed in the binded datagrid also contains other rows that has this prodname:

ＣＲＦ湯けむり紀行ＳＦ－Ｔ
ＣＲぱちんこハイサイＶＲ１
ＣＲホワイトエンジェルＦＳ

etc.

Why does the command yields other records where the prodname field contains the 'CR' thing?

Sample querry result in a datagrid:

field1 field2 field3 prodname

a as er ＣＲ新海物語スペシャルＭ８

w aa ww ＣＲＦ湯けむり紀行ＳＦ－Ｔ

ss dd ee ＣＲぱちんこハイサイＶＲ1

sdds ddd ee ＣＲ新海物語スペシャルＭ８

rf d5r wwe ＣＲぱちんこハイサイＶＲ１

Does this strange and redundant output has something to do with the Japanese characters contained in some fields

of the table?

Thanks in advance for your help.

|||Try with the correct nvarchar syntax:

SELECT * FROM table WHERE prodname = N'ＣＲ新海物語スペシャルＭ８'