Clever Geek Handbook

SQL Server Linked Servers

I want to create a linked server on one sql server with another using sp_addlinkedserver procedure. When I access the remote server, I would like it to be logged in as me (i.e. using my Windows account). How to do it?

+1


source to share


4 answers


It can be insane to customize. Check out this related question:

https://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 using the old fashioned Sql Login. You have to do a lot of what I consider to be risky changes to your domain's security settings to get it to work with a domain account.

+1


source


You can do this with the sp_addlinkedsrvlogin routine:

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


It assumes that you are logging into the server where the link was created using Windows Authentication.

+1


source


Following from Ed Harper above:

You need to set the Security Account Deficit for the SQL service account to pass through your login token.

Edit:

This is not a SQL problem. Delegation / Pass-through Authentication is a Windows / AD feature.

It is also used to enable the intranet website to use the end user login to authenticate to the SQL box, where (of course) the web server is a separate block. In this case, you are configuring the web server for delegation.

In the OP's script, we are allowing the 1st SQL block to delegate credentials in the second field.

Edit2:

Another belt explains this as well.

Finally, it has nothing to do with SQL inputs.

+1


source


Use SQL Management Studio to view the properties of the linked server. There you will find your login information

-1


source






More articles:


All Articles