Login failed for user - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Author

Topic

smrc
Starting Member

1 Post

Posted - 2008-10-02 : 04:04:40

Hello,

First of all I have to say I am new to SQL Server. Let me explain what is the problem. I have 2 Win2003 servers on my laptop, one called A and the other one is on VMware called B. Both of them have their instances of SQL Server 2005.

Now I want to copy a database via Integration services "Transfer Databese task" from A to B..When I try that I get the following error:

Failed to connect to server B.
Login failed for user ''. The user is not associated with a trusted SQL Server connection.

I can ping B from A and vice versa, so the "machines" see each other. I also found that there is a "mixed" authentication mode and I enabled that on SQL Server on A and B, and still the error is not gone..

Any help is appreciated (Sorry for my bad english)..

lepeniotis
Yak Posting Veteran

75 Posts

Posted - 2008-10-02 : 04:36:46

Configure SQL B as a linked server to impersonate the remote login.
You need to define a linked server for each external data source you want to access and then the security context under which your distributed queries will run. You can configure the linked server to use one of the following security modes:

Self mapping when a linked server is created, this mode is added for all local logins, so SQL Server tries to connect to external data source using the current user’s login credentials. The same login and password must exist on the remote Server. This is the default behaviour.
Remote Credentials – This mode lets you map local logins to remote logins on the external data source.

I hope it will help.
Regards
Lepeniotis

MSc Advanced Computing Science
MSc Database Professional
Sheffield Hallam University
MCP (70-229, 70-228)
Industrial IT Engineer

lepeniotis
Yak Posting Veteran

75 Posts

Posted - 2008-10-02 : 04:43:22

You can also have a look at the msdn for how you can do it!
http://msdn.microsoft.com/en-us/library/ms175537.aspx

MSc Advanced Computing Science
MSc Database Professional
Sheffield Hallam University
MCP (70-229, 70-228)
Industrial IT Engineer

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-02 : 09:36:18

Why can't you do this with simple Backup/Restore Method?

lepeniotis
Yak Posting Veteran

75 Posts

Posted - 2008-10-02 : 10:19:11

Is it the only thing that you want to transfer the database or you want to test the connectivity between the servers?? Sorry if I understood wrong but if this only what you want you have to do what sodeep said.

MSc Advanced Computing Science
MSc Database Professional
Sheffield Hallam University
MCP (70-229, 70-228)
Industrial IT Engineer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources