| Author |
Topic |
|
actsql
Starting Member
34 Posts |
 Posted - 2010-05-28 : 16:22:27
|
| I have two SQL servers running 2005. #1 is SQL authentication and #2 is Windows authentication. In #1 I setup a linked server to #2 using SSMS. However, whenever I try to run a query against #2 from within #1 I get a login error. The login is either state 5 (no user ID) or, state 8 (wrong password). I have used every combination of user id / password I can think of (windows network, the sa access) without success. There are logins setup for each of the aforementioned in server #2. I have researched this issue and wonder if it is an issue of no "double-hop" on the login due to the way kerberus / NTML was setup, but I am grasping at straws as I am not a trained administrator.How can I make #2 work as a linked server to #1? Is it something simple within SQL server or, is it an issue with the authentication (Kerberus / NTML)? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-28 : 16:35:30
|
| Can you instead use SQL authentication on #2? The easiest way to configure the linked server is to specify that the current security context be used, and then you connect to #1 using SQL authentication and then those same credentials are passed to #2. That's assuming that you've got the same SQL account and password on #1 and #2. I use sa to connect when I need to query a linked server.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
actsql
Starting Member
34 Posts |
Posted - 2010-05-29 : 13:26:14
|
| I don't think changing #2 over to sql authentication is an option (due to internal politics, etc.) That being the case, is there an option to do linked server between #1 and #2 if #2 is still windows authentication. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
actsql
Starting Member
34 Posts |
Posted - 2010-05-30 : 16:43:27
|
| Thank you. Do you by chance have a link to any resource that would walk us through that process? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
actsql
Starting Member
34 Posts |
Posted - 2010-05-31 : 10:34:15
|
| Would changing to the option of sql or window authorization work (as opposed to just window or just sql)? Also, right now the app users are logging in with their network ids (windows). So if we changed the security option on the database side would it impact the users on the app side? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
actsql
Starting Member
34 Posts |
Posted - 2010-06-01 : 08:30:11
|
| Thank you for your advice. It is much appreciated. I think I will try to convince everyone we need to go to mixed mode. |
|
|
|
|
can't login to linked server