Linked Servers security

  • Environment: SQL2k sp3 on 2 W2k sp3 servers.

    From Server1, would like storedproc to access a table in DB on Server 2. Via EM, I created a linked server. When I open the server and click on Tables I get:

    Error 17: SQL Server does not exist or access denied.

    I'm the dbo. I'm a domain admin. Both databases start with the same domain user account.

    What am I not doing correctly. I could have swore that I've done this in the past.

    Thanks

  • Are you connecting via Windows auth over the linked server connection? If so, are you getting this error coming from a client or from EM on the SQL Server itself (either at the console or through Terminal Services)?

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • I'm using EM at my workstation to connect to all SQL Servers via Windows Authentication.

  • Is the linked server connection between the 2 SQL Servers also Windows auth?

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • Yes

  • Unless you're in Active Directory and you've configured delegation properly, you're running into the NTLM double-hop issue. One of the two connections, without using delegation, will have to be a SQL Server connection.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • How do I confirm that Active Directory and Delegation are setup correctly ?

  • Ask your system administrators whether you're on Active Directory or still using NT 4 domain controllers exclusively. From there, they should be able to help you with delegation. The user accounts must be set so they can be delegated and the server must be set to be able to delegate. This will require your sysadmins.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • We are using Active Directory. If you have a specific KB article, I'll get my administrators to take a look.

    On the Linked Server Properties (Security Tab) I have selected "Be made using the login's current security context" because I want integrated security used.

    On the database server properties on both SS2K-SP3 servers, I haved checked 'Allow cross-database ownership chaining'

    Both servers start SQL Server with the same startup service account.

    When I click on the Linked servers tables, I get Error 18456: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Viewing 9 posts - 1 through 8 (of 8 total)

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