Geek Answers Handbook

How to configure authentication between linked servers?

I am trying to verify a proof of concept that I can run a distributed transaction on two linked SQL servers linked using sp_addlinkedserver - their names are Server1 and Server2, both of which are executed by default. Each server contains one database, the source and destination, respectively, and the destination database contains a separate table called "Output", i.e.

Server1.Source Server2.Destination.Output

The OUTPUT table has the following structure:

 OUT_PKEY int identity(1,1) primary key, OUT_TEXT nvarchar(255)

From Server1, I called sp_addlinkedserver 'Server2' to link the two databases, and I tried to run the following query to check if this link really works:

 Select * From Server2.Destination.dbo.Output

The following exception was returned to me:

Access to the remote server is denied because there is no name mapping.

Fairly enough, so from Server1 I run sp_addlinkedsrvlogin 'Server2', which according to the documentation says that it should accept the credentials of the user who is executing the request remotely (i.e. from server 1) and apply these credentials to Server2. This means that since I am connected to Server1 using Windows authentication, this should mean that my Windows credentials also apply to Server2.

Now the error message changes to:

Login failed for user "NT AUTHORITY \ ANONYMOUS LOGON".

Having Googled this exception, I came up with nothing useful that pointed me in the right direction. What am I missing? I would expect that [if the login fails] the exception will refer to my Windows credentials, and not to anonymous login credentials.

It seems that as soon as I get the work of the link itself, the distributed transactions themselves should be pretty simple - the documentation implies that I just need to make sure that the DTC service is running on Server1, and that any requests made on Server1 will be broadcast by reference :

  • Turn SET XACT_ABORT ON until my distributed transaction is initialized
  • I am using BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION
  • If I want to reference a non-standard instance of SQL Server on Server 2000, I replace any instances of Server2 in my query with [Server2 \ InstanceName]

My questions are as follows:

  • How do I get through this problem? The stored procedure sp_addlinkedsrvlogin itself does not seem to do this trick.
  • Is it really as simple as starting a distributed transaction, as the documentation suggests?

TIA

+4
source share
2 answers

If you are in a domain, then the parameter should be "Done using the current login security context", but there is one more step - you need to provide the SPN to each of the servers involved in the transaction.

Assuming you are running SQL services on both servers as a domain user (what you need to do this job is LocalSystem will not do this), here are the instructions you will need:

http://technet.microsoft.com/en-us/library/bb735885.aspx

Remember that the user will need an SPN for both servers, but not for the client - for example, if you are going from client → server1 → server2, the SQL Service account will need SPN for servers server1 and server2.

If you are confused (this is a confusing process), post a comment and I will clarify the instructions.

+1
source

Assuming these servers are in the same domain - did you enable trusted delegation so that your server can transfer credentials to the target server? You would pull the Active Directory object for the server and go to the "Delegation" tab and select "Trust this computer only to delegate only the specified services", and then enter the SQL Server data so that the server is allowed to transfer credentials:

Service Type = MSSQLSvc
User / Computer = YourTargetServer.Your.Domain
Port = 1433

Unfortunately, many of these types of authentication problems with linked servers require that the reboot take full effect (therefore, if these are production servers, it is difficult to fix the problem within a day).

As for distributed transactions - if you ended up connecting to the connected server correctly, then distributed transactions work just fine. Although the next thing you are likely to encounter when you earn it, there is a huge flaw that you cannot use any form of SCOPE_IDENTITY (), @@ IDENTITY, etc. To retrieve the primary keys after inserting something into the linked database. But this is another problem with her own funny workarounds ...

0
source

More articles:


All Articles