SQL Server linked servers

Question

I want to create a linked server in one sql server to another using the sp_addlinkedserver procedure. When i access the remote server I would like it to logon as me (i.e. using my windows account). How do I do this?

Answer 1

Use SQL Management Studio to see the properties for the linked server. There you will find your logon information

Answer 2

You can do this with the sp_addlinkedsrvlogin procedure:

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'<your linked server name>',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

This assumes you log in to the server on which the link was created using Windows authentication.

Answer 3

This can be maddening to set up. Check this related question:

http://stackoverflow.com/questions/33153/sql-server-to-sql-server-linked-server-setup

With Sql Server 2005 on a 2K3 domain, the most secure way to set up a linked server, unfortunately, is probably to use an old fashioned Sql Login. You have to make a lot of what I consider risky changes to your domain security settings to get it to work with a domain account.

Answer 4

Following on from Ed Harper above:

You need to set "Security Account Delegation" for the SQL service account to enable it to pass through your login token.

Edit:

This is not a SQL problem. Delegation/pass through authentication is a Windows/AD feature.

It's also used to enable an intranet web site to use the end user windows login to authenticate onto the SQL box where (of course) the web server is a separate box. In this scenario, you configure the web server for delegation.

In the OP's scenario, we are enabling the 1st SQL box to delegate credentials onto the 2nd box.

Edit2:

The other thread explains this too.

Finally, nothing to do with SQL logins.

Answer 5

Thanks for the answers. Windows/AD are not my area, so I did as Eric suggested and settled for an SQL Login.