Sql-server – How to access to DB Instance from another DB Instance with Windows Authentication

access-controlSecuritysql-server-2008-r2

I have two database servers

  • DB_INSTANCE1
  • DB_INSTANCE2

Is it possible to access from DB_INSTANCE1\DB1 SQL script to DB_INSTANCE2\DB2 tables without using SQL Logins? (not every database servers are in MIXED mode)

Can I somehow set this in terms to use only AD security groups or DB roles?
What is the recommendation practice?

Best Answer

You will have to setup a linked server forwarding the user credentials between the instances. There are step by step instructions here:

It boils down to running this on DB_INSTANCE1\DB1

EXEC sp_addlinkedserver   
 @server=N' DB_INSTANCE2', 
 @srvproduct=N'',
 @provider=N'SQLNCLI', 
 @datasrc=N' DB_INSTANCE2\DB2';

sp_addlinkedsrvlogin @rmtsrvname = 'DB_INSTANCE2', @useself = 'TRUE'

Which creates a linked server named db_instance2 and tells the server to use the logged on user credentials to connect to the linked server. If you have configured Kerberos correctly for DB_INSTANCE1 users with permissions on both the servers will be able to select data from the other server: select * from db_instance2.master.sys.databases

As stated before, for this to work Kerberos has to work and therefore you will need to setup SPN's and do some active directory magic. The Kerberos configuration manager will help you and your AD administrator to set up everything needed

It also means that users that connect with database logins will not be able to connect to the server - For that you will have to create user mappings either in the GUI or using TSQL

sp_addlinkedsrvlogin 
 @rmtsrvname = 'DB_INSTANCE2', 
 @locallogin = 'Local_SQLLogin', 
 @rmtuser = 'RemoteSQLLogin', 
 @rmtpassword = 'rmtpassword'