patternsqlModerate
Linked Server with Windows Authentication Permissions
Viewed 0 times
permissionswithauthenticationwindowsserverlinked
Problem
I’m trying to figure out how to setup a linked server object from SQLServerA to SQLServerB using Windows Authentication.
I’ve setup many linked servers but have always used a SQL Server login which works fine.
In this scenario, a logon has been created at SQLServerB using Windows Authentication granting access to a specific database.
From SQLServerA, I’m creating a linked server object to SQLServerB. On the Security Page of the linked server configuration, I’m using "Be made using this security context" and enter Remote login:domain\user, With Password: password respectively.
When SQL performs the Test Connection final step, I receive an error indicating “Login failed for user ‘domain\user’ (Microsoft SQL Server, Error: 18456)”.
Is there something special I need to do to create the linked remote server using Windows Authentication as the authorization type instead of a SQL Server login?
If that’s not possible, is there another solution that I can use to query a remote servers other than interactively through SSMS?
My goal is to setup SQL job(s) to import some data from the remote SQL Server.
Thanks
I’ve setup many linked servers but have always used a SQL Server login which works fine.
In this scenario, a logon has been created at SQLServerB using Windows Authentication granting access to a specific database.
From SQLServerA, I’m creating a linked server object to SQLServerB. On the Security Page of the linked server configuration, I’m using "Be made using this security context" and enter Remote login:domain\user, With Password: password respectively.
When SQL performs the Test Connection final step, I receive an error indicating “Login failed for user ‘domain\user’ (Microsoft SQL Server, Error: 18456)”.
Is there something special I need to do to create the linked remote server using Windows Authentication as the authorization type instead of a SQL Server login?
If that’s not possible, is there another solution that I can use to query a remote servers other than interactively through SSMS?
My goal is to setup SQL job(s) to import some data from the remote SQL Server.
Thanks
Solution
The "Be made using this security context" doesnt support Windows Authentication.
Be made using this security context Specify that a connection will be
made using the login and password specified in the Remote login and
With password boxes for logins not defined in the list. The remote
login must be a SQL Server Authentication login on the remote server.
You need to use this:
When connecting to the local server using Windows Authentication
(recommended), select Be made using the login's current security
context to connect to the remote server using the same Windows
Authentication credentials.
Or you could use "Not be made" and specify the login with impersonate. Requires that the user exists on both servers aswell.
You can read more here: https://technet.microsoft.com/en-us/library/ms188477(v=sql.105).aspx
Be made using this security context Specify that a connection will be
made using the login and password specified in the Remote login and
With password boxes for logins not defined in the list. The remote
login must be a SQL Server Authentication login on the remote server.
You need to use this:
When connecting to the local server using Windows Authentication
(recommended), select Be made using the login's current security
context to connect to the remote server using the same Windows
Authentication credentials.
Or you could use "Not be made" and specify the login with impersonate. Requires that the user exists on both servers aswell.
You can read more here: https://technet.microsoft.com/en-us/library/ms188477(v=sql.105).aspx
Context
StackExchange Database Administrators Q#182985, answer score: 10
Revisions (0)
No revisions yet.