Post reply

create linked server using windows authentication

  • 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

  • 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" 😉

  • 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?

  • 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]

  • 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" 😉

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply