remote sql server login fails

Hello,

I'm about googled out on this one. I did find one post that looked almost like mine but the thread was never finished. I'm sure this problem has been solved and I'm just having trouble finding it. Can someone help me?

I'm running sql server 2000 on a small workgroup network of winxp machines.

If sql server machine is the same as webserver machine, my asp.net app works. If sql server and webserver are different, the app fails because of sql server login failure.

I am able to explore the database remotely through server explorer in VStudio2003. However, when I try to access the database with the asp.net application, the login fails. I have tried several connection strings. Authentication on the server is 'mixed', the server runs in the system account, and the password for sa is "" (blank password).

I've tried (for instance)
Application["DBConnectString"] = "server=machinename;uid=sa;pwd=;database=Resume";
and
Application["DBConnectString"] = "server=machinename;integrated security=true;database=Resume";

When "machinename" is the same as the machine on which the app is running. both of those connection strings work, and the application runs correctly.

When "machinename" is different (that is, sql server on a different machine from the webserver), depending on the connection string I get either
"Login failed for user 'sa'."
or
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."

Obviously VStudio is using some mechanism to add the remote server to it's list of servers and then to allow me manually to explore the databases on that server. Why am I too stoopid to figure it out?

TIA,
Timuse the IP.

or go to www.able-consulting.com, and click on technology and look up how to use connection strings.|||I'm sorry but your reply doesn't make any sense to me. I did try using the IP address and got the same errors. Was your suggestion that I should learn how to use connection strings meant to be sarcastic?

Maybe someone who knows the answer will post.|||no I wasn't being sarcastic. that site is my main resource for connection strings.

also, it's not uid, it's user id. :) my bad. can't believe I missed that. and pwd is password.|||Well, like I said in the original post, the connection strings both work just fine when the sql server and web server are the same, so I don't understand what you meant about learning about connection strings.

Maybe someone who knows the answer will post...|||I've managed to solve half the problem. Apparently the remote server will not allow sa to login over the network when the password for sa is "". I changed it to a non-blank password and now the following works:
Application["DBConnectString"] = "server=machinename;uid=sa;pwd=password;database=Resume";

Please forgive me for having a blank sa password. The workgroup is behind a firewall and the only substantial databases are northwind and pubs, so I figured I was safe. :-P

So, I guess I am more curious than desperate now. I would really rather get the integrated security form of the connection string to work, because I don't like the idea of hard coding passwords into the connection strings. Is integrated security possible in a workgroup setting?

TIA,
Tim|||You should be able to use integrated security.

Your connection string should look like this: "Server=machinename;Database=Resume;Trusted_Connection=True;"

Presumably you are receiving this error with that connection string:
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."

This means that the Microsoft Windows user account that IIS uses to process the request for the web page does not have a corresponding NT Authentication login in the remote SQL Server. Read up on this issue here:PRB: ASP/ODBC/SQL Server Error 0x80040E4D "Login Failed for User '(Null)'". Be sure to read theAuthentication methods for connections to SQL Server in Active Server Pages andINFO: Accessing SQL Server with Integrated Security from ASP links as well.

Terri|||Thank you for thinking about this. I agree with you: I should be able to use integrated security. I'm beginning to think integrated security is a little bit of a fudge with workgroups, however, and that may be my problem.

I had run across those KB articles before but re-read them at your suggestion. I have tried some of the recommendations without success. Some seem out of date, though. Also, the warning that I got in response to the workaround in KB 176379 against using it was so explicitly scary that I undid the change before I even tested it.

Another KB article mentioned a change that would result in the user being required to login when accessing the remote page. I don't think that is a good idea, especially if it happens every time I want to open/re-open a connection to the database, but it shouldn't be necessary at all, since the user is already logged in to Windows and that's what the idea of integrated security is about, right?

I suspect the problem is related to the fact that the network is a workgroup, with no domain controller to authenticate the user in a totally acceptable way. In my situation, for instance, file sharing works between machines as long as the user is defined on all machines with the same name and password, but I have noticed I'm not able to access my own data in the "Documents and Settings" folder of a machine across the network, even though the drive is shared and I have access to other files on the drive and I'm logged in as the same user as the owner of that folder on that machine. Perhaps SQL and/or IIS are having similar glitches.

What I find particularly exasperating is that, without making any changes to any of the servers' settings, VStudio is able to have its way with the databases on the remote machine, via the Studio Server Explorer. Obviously there is some mechanism by which it is sending my credentials, or someone's credentials. I'm just not seeing the secret incantation in any of the info I've looked at so far.

If you have ever gotten this to work for yourself, and as I said I think the problem is to do with the fact that it's a workgroup although I'd be happy to be proved wrong in that regard as long as I get it working eventually, I'd really like to see specifically the solution. And again, it's a WinXP (Pro) workgroup. Some of the KB articles refer to problems with NT4 that were fixed in Win2000. Not clear whether WinXP "improvements" to Win2000 may have made it easier or harder to do what I want to do.

Thanks again for your suggestions.

Remote Proc Restore Fails

I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
TerryI should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posting.google.com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:
> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>

Remote Proc Restore Fails

I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
TerryI should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posti
ng.google.com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:

> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

Remote Proc Restore Fails

I have set up a job to implement simple Log Shipping for SQL Server
2000. I have added a link on my primary server so I can execute a
remote stored proc on the standby server from the SQL Agent job on the
primary. The remote procedure restores the backup copied over from the
primary server.
When the remote procedure executes, the job reports success, but the
standby server is left in the Loading state or Loading/Suspect on some
executions. The procedure is:
RESTORE DATABASE MPR
FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
STATS = 5
When I execute the procedure on the standby using SQL Query Analyzer
after a failure, it restores successfully! It only fails when it runs
from the job on the primary server. But the job history says it was
successful.
Can anyone help me troubleshoot this problem? I don't know where to
begin.
Terry
I should mention that I have 12 other databases using similar
procedures to implement log shipping and I have never seen this
problem before on any of them. The only apparent difference is that
this DB is much bigger 15GB and uses multiple file groups.
dontsendmecrud@.hotmail.com (Terry) wrote in message news:<e2c86606.0407080535.11f95f63@.posting.google. com>...
> I have set up a job to implement simple Log Shipping for SQL Server
> 2000. I have added a link on my primary server so I can execute a
> remote stored proc on the standby server from the SQL Agent job on the
> primary. The remote procedure restores the backup copied over from the
> primary server.
> When the remote procedure executes, the job reports success, but the
> standby server is left in the Loading state or Loading/Suspect on some
> executions. The procedure is:
> RESTORE DATABASE MPR
> FROM DISK = N'\\MPR01\SQLLogShip\MPR_backup_device.bak'
> WITH REPLACE, STANDBY = N'\\MPR01\SQLLogShip\undo_MPR_Data.ldf',
> MOVE N'MPR_Data' TO N'E:\SQLData\MSSQL\Data\MPR_Data.mdf',
> MOVE N'MPR_Index' TO N'D:\SQLIndex\MPR_Index.ndf',
> MOVE N'MPR_Log1' TO N'E:\SQLData\MSSQL\Data\MPR_Log1.ldf',
> MOVE N'MPR_Log2' TO N'E:\SQLData\MSSQL\Data\MPR_Log2.ldf',
> STATS = 5
> When I execute the procedure on the standby using SQL Query Analyzer
> after a failure, it restores successfully! It only fails when it runs
> from the job on the primary server. But the job history says it was
> successful.
> Can anyone help me troubleshoot this problem? I don't know where to
> begin.
> Terry
|||Hi - I have exactly the same problem, with the standby database in a
state of loading after the database restore - rather than in read only.
I can run each step in the job manually and it works fine - only when
run from the remote server does it seem to fail.
Any help would be appreciated.
Thanks,
Serena.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi - Found the issue - the linked server has a query timeout connection
setting - this was set to 0 which means it uses the remote query timeout
setting in sp_configure - this was set to 10 minutes...hence the failures.
"Serena Barker" wrote:

> Hi - I have exactly the same problem, with the standby database in a
> state of loading after the database restore - rather than in read only.
> I can run each step in the job manually and it works fine - only when
> run from the remote server does it seem to fail.
> Any help would be appreciated.
> Thanks,
> Serena.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

Remote DTSX execution, when sql25k fails.

Hi everyone,

This is my snippet of code. It works fine with remote DTSX but when they have got connections against Sql Server 2005, ending given the following error:

Event: (On error)

12:21:52,-1071611876,0x,Error de la llamada del mtodo AcquireConnection al administrador de conexión "LOCALHOST.BDAplis"

That means more or less "Error on calling method AcquireConnection to Connection Manager localhost...

It's curious. I've got other packages which are using Sql Server 2000 connections without errors.

Thanks for your help,

pkgResults = pkg.Validate(Nothing, Nothing, EventsSSIS, Nothing)

If pkgResults = DTSExecResult.Success Then

pkg = app.LoadPackage(ActObject.packageName, Nothing, True)

endif

pkg.InteractiveMode = False

pkgResults = pkg.Execute()

Is this really remote execution? SSIS is really a client side tool. The user trying to make the connection to the SQL server, through a SQL connection in the package, is the user running your code. So who is running your code, and do they have rights to connect to your SQL Server? I would check the SQL server error log. Good practice says we have set the server level security option to log failed connections, and that should tell you who the user is. Alternatively run a SQL Server profiler trace against the server concerned, and select the login failed event. When the failure happens, you should see the event in the profiler output, and can see who the user is.|||

Hi DarrenSQLIS,

Thanks for you reply.

Ok, my user domain is doing the connection but how to select for LoadPackage(,,,) method another user, an Administrator user, for example.

Otherwise loadfromsqlserver offer the possibility to define user and password.

thanks

Remote Data Access (RDA.Pull)

Hi,

I am trying to use RDA.pull to pull data from SQL server 2000 to sql mobile, but it fails when some columns are included in the SQLSelectString, though the datatypes involved are only char, float, smallint and smalldatetime, and nothing is longer than 30. Since there is a very large number of columns, it is difficult to isolate the guilty one by trial and error! Coud someone please help?

- Nag Rao

Can you please try pulling a table with no.of columns less than 128!

Thanks,

Laxmi

Remote Data Access (RDA.Pull)

Hi,

I am trying to use RDA.pull to pull data from SQL server 2000 to sql mobile, but it fails when some columns are included in the SQLSelectString, though the datatypes involved are only char, float, smallint and smalldatetime, and nothing is longer than 30. Since there is a very large number of columns, it is difficult to isolate the guilty one by trial and error! Coud someone please help?

- Nag Rao

Can you please try pulling a table with no.of columns less than 128!

Thanks,

Laxmi

Remote connections to named instances fails on SQL 2005 cluster

I have a new SQL Server 2005 cluster with multiple named instances of SQL
Server 2005 running. From any cluster machine, I can connect to any instanc
e
of SQL Server running in the cluster. From remote machines, I can connect t
o
the default instance of SQL Server, but I can not connect to any named
instances. Help?
+ SQL Browser is running on one physical machine in the cluster
+ Surface Area Configuration has "remote clients" enabled for all instances
+ Named pipes and TCP/IP are enabled
+ DTC is running on the cluster. It is configured to allow network access
on each physical machine.
+ Windows firewall is disabled. Remote machines on same subnet as cluster
machines.
+ Client machine can ping virtual servers?
After waiting a few hours, remote connections now work
?
"Bill Q" wrote:

> I have a new SQL Server 2005 cluster with multiple named instances of SQL
> Server 2005 running. From any cluster machine, I can connect to any insta
nce
> of SQL Server running in the cluster. From remote machines, I can connect
to
> the default instance of SQL Server, but I can not connect to any named
> instances. Help?
> + SQL Browser is running on one physical machine in the cluster
> + Surface Area Configuration has "remote clients" enabled for all instance
s
> + Named pipes and TCP/IP are enabled
> + DTC is running on the cluster. It is configured to allow network access
> on each physical machine.
> + Windows firewall is disabled. Remote machines on same subnet as cluster
> machines.
> + Client machine can ping virtual servers

Remote connection to SSIS fails with Access is Denied

I'm new to SSIS and I'm having problems getting a remote connection to the SSIS service using Management Studio on my workstation. If I terminal Service onto the Server I have no problems connecting to SSIS, but if I try to connect remotely I get the "Access is denied" error message. I have completed the following steps:

To configure rights for remote users on Windows Server 2003 or Windows XP

1. If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.

2. Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.

3. Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.

4. Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.

5. Right-click on MsDtsServer and select Properties.

6. In the MsDtsServer Properties dialog box, select the Security tab.

7. Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

8. In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.

9. Click OK to close the dialog box. Close the MMC snap-in.

10. Restart the Integration Services service.

But I still get the Access is denied error from my workstation?

I have Power User rights on the server and I'm a sysadmin in the database instance. The SSIS packages I am trying to access are stored in the database. If I add myself to the local administrators group on the server I CAN get remote access, but this is not an acceptable solution in our production environment.

Thanks for any help

Please search the forums... You'd find your answer there, however:

http://www.ssistalk.com/2007/04/13/ssis-access-is-denied-when-connection-to-remote-ssis-service/|||Thank you

Remote Connection Fails with Web Service

Hello-
I am having a problem with a Web Service written in Visual Studio 2005.
I have a typed dataset that I created using the Visual Studio 2005
Data Source tool. This retrieves its data from a SQL Server 2000
database. My client calls a web service which calls a business
component to retrieve the typed dataset (i.e. Client --> Web Service
--> Business Component).
Whenever I put the web service between my client and my business
component I get the following error:
Server was unable to process request. --> An error has occurred while
establishing a connection to the server. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default
settings SQL Server does not allow remote connections. (provider: Named
Pipes Provider, error: 40 - Could not open a connection to SQL Server)
If I do not put my web service in between my client and business
component, I do not get this error (i.e. Client --> Business
Component).
I have made sure that my SQL 2005 has Remote Connections enabled with
both Named Pipes and TCP/IP.
I don't understand why putting a web service between my client and
business component is making this error appear or how to remedy the
issue. Any help would be much appreciated!
-DrewI found the problem. In my web.config I had to add the following
configuration: <identity impersonate="true" />.
-Drew