create linked server using windows authentication

• Mohammed Imran Ali

  SSCrazy

  Points: 2125

  More actions

  July 16, 2013 at 12:16 am

  #295548

  Hi,

  I have a peculiar requirement but was not able to go much ahead.

  Setup a linked server on InstA(mixed mode authentication) to fetch data from InstB(Windows Only). The user accessing the linked server is a SQL Authenticated user on InstA. When i use the below script (from another post in sqlservercentral.com).

  DECLARE @strLinkedServer NVARCHAR(100)

  SELECT @strLinkedServer = 'SRV2\InstB'

  EXECUTE master.dbo.sp_addlinkedserver

  @strLinkedServer,

  'SQL Server'

  EXECUTE master.dbo.sp_addlinkedsrvlogin

  @rmtsrvname = @strLinkedServer,

  @useself = N'False',

  @locallogin = N'sqluserA', -- add local login

  @rmtuser = N'Domain1\user', -- add the remote login

  @rmtpassword = 'StrongPassword' -- add the remote login password

  This seems to work in terms of creating the linked server. While trying to use it, i get the error

  [font="Courier New"]Msg 18456, Level 14, State 1, Server SRV2\InstB, Line 1

  Login failed for user 'Domain1\user'.[/font]

  I get the below error in the SQL Server Error logs on InstB

  [font="Courier New"]Login failed for user 'Domain1\user'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 10.1.1.11][/font]

  Looks like I would not be able to create a linked server mapping a local SQL Authenticated user to a domain user account for the remote login. Is there a work around for this?

  Cheers

• Perry Whittle

  SSC Guru

  Points: 234040

  More actions

  July 16, 2013 at 12:36 am

  #1632764

  Add the domain user to instA and use impersonation.

  Do you have your SPNs set up correctly otherwise windows authentication will still fail?

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs"

• Mohammed Imran Ali

  SSCrazy

  Points: 2125

  More actions

  July 16, 2013 at 10:38 pm

  #1633069

  Thank you for the info on SPNs. I had checked the SPNs are registered under the SQL Server Service account names for both Instance name and the Port#( this is a cluster installation).

  H:\>setspn -L Domain1\SQLSrvce.InstA

  Registered ServicePrincipalNames for CN=SQLSrvce.InstA,OU=SQL Server Accounts,OU=Service Accounts,DC=Domain1,DC=com:

  MSSQLSVC/InstA.domain1.com:59101

  MSSQLSVC/InstA.domain1.com:InstA

  H:\>setspn -L Domain1\SQLSrvce.InstB

  Registered ServicePrincipalNames for CN=SQLSrvce.InstB,OU=SQL Server Accounts,OU=Service Accounts,DC=Domain1,DC=com:

  MSSQLSVC/InstB.domain1.com:59102

  MSSQLSVC/InstB.domain1.com:InstB

  This link http://blogs.msdn.com/b/sql_protocols/archive/2005/10/12/479871.aspx shows to register SPN which is already done in my case. But still the [font="Courier New"]auth_scheme[/font] still shows as NTLM instead of KERBEROS. Any other options that i would need to change?

• CarlaAbanes

  Right there with Babe

  Points: 782

  More actions

  July 17, 2013 at 1:58 am

  #1633116

  I also had this same scenario way back. Our domain admin needed to do something in the AD, something to do with policy, then he asked me restart my sql server and after that everything worked OK.

  Cheers!
  [/url]

• Perry Whittle

  SSC Guru

  Points: 234040

  More actions

  July 17, 2013 at 2:06 am

  #1633123

  Mohammed Imran Ali (7/16/2013)

  ---

  Thank you for the info on SPNs. I had checked the SPNs are registered under the SQL Server Service account names for both Instance name and the Port#( this is a cluster installation).

  H:\>setspn -L Domain1\SQLSrvce.InstA

  Registered ServicePrincipalNames for CN=SQLSrvce.InstA,OU=SQL Server Accounts,OU=Service Accounts,DC=Domain1,DC=com:

  MSSQLSVC/InstA.domain1.com:59101

  MSSQLSVC/InstA.domain1.com:InstA

  H:\>setspn -L Domain1\SQLSrvce.InstB

  Registered ServicePrincipalNames for CN=SQLSrvce.InstB,OU=SQL Server Accounts,OU=Service Accounts,DC=Domain1,DC=com:

  MSSQLSVC/InstB.domain1.com:59102

  MSSQLSVC/InstB.domain1.com:InstB

  This link http://blogs.msdn.com/b/sql_protocols/archive/2005/10/12/479871.aspx shows to register SPN which is already done in my case. But still the [font="Courier New"]auth_scheme[/font] still shows as NTLM instead of KERBEROS. Any other options that i would need to change?

  You're getting NTLM auth as the SPNs you report do not seem correct.

  Typically for a clustered instance you would see

  MSSQLSvc/virtualnetworkname.domain.co.uk:tcpportnumber

  MSSQLSvc/virtualnetworkname:tcpportnumber

  Ensure you delete all other duplicates. You must also trust the computer account for delegation.

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs"