Post reply

KERBEROS

  • January 9, 2009 at 1:35 pm

    #125571

    Guys

    I did set up KERBEROS authentication recently and looked fine after my test but till i see a problem

    When i run the below query

    select * from LinkServerRev.master.dbo.sysdatabases

    It works fine BUT when i run this

    select * from LinkServerRev.PA_Rev.dbo.Joblist

    it gives me this error.

    Msg 7314, Level 16, State 1, Line 1

    The OLE DB provider "SQLNCLI" for linked server "LinkServerRev" does not contain the table ""PA_Rev"."dbo"."Joblist"". The table either does not exist or the current user does not have permissions on that table.

    This is my link server setup

    EXEC sp_addlinkedserver @server=LinkServerRev,

    @srvproduct='',

    @provider='SQLNCLI',

    @datasrc='',

    @provstr='Integrated Security=SSPI'

  • January 9, 2009 at 1:42 pm

    #924559

    Hi Mike,

    I don't mean to be mr Obvious here but does your domain/login have permission to PA_Rev.dbo.Joblist ?

    You never know it's friday and ... 😛

    [edit] grammar...

  • January 9, 2009 at 1:44 pm

    #924560

    This has nothing to do with Kerberos. The error is saying that either the table that you're querying does not exist, or the user that you're connecting across the linked server with does not have permissions to access that table.

    Can you check and confirm that the table does exist on the remote database?

    What are the security settings of the linked server?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 9, 2009 at 1:50 pm

    #924566

    I did check, it has permissions over that database and the tables.

  • January 9, 2009 at 1:55 pm

    #924572

    What are the security settings of the linked server?

    Are you sure the linked server is pointing at the correct database on the remote server?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 9, 2009 at 2:22 pm

    #924599

    Be made using current security context.

  • January 9, 2009 at 2:31 pm

    #924605

    When you run the second query, do you see a login failure on the second SQL Server?

    K. Brian Kelley
    @kbriankelley

  • January 9, 2009 at 2:39 pm

    #924612

    yes i see that message.

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 172.16.20.34]

    I thinkg to update when run the 1st query on master db's it is listing all the db's from the current instance not on the remote instance.

  • January 9, 2009 at 2:52 pm

    #924625

    Now I get this message

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    OLE DB provider "SQLNCLI" for linked server "LinkServerRev" returned message "Invalid connection string attribute".

  • January 9, 2009 at 2:52 pm

    #924627

    Are both SQL Servers set up for Kerberos authentication (including SPNs in Active Directory) and the first SQL Server set up further for Kerberos delegation to the second?

    K. Brian Kelley
    @kbriankelley

  • January 9, 2009 at 2:56 pm

    #924631

    yes, both are configured for KERBEROS

  • January 9, 2009 at 5:50 pm

    #924718

    But there's a significant difference between configuring for Kerberos authentication and Kerberos delegation. Kerberos delegation requires configuration beyond the SPNs.

    K. Brian Kelley
    @kbriankelley

  • January 12, 2009 at 11:27 am

    #925433

    KERBEROS authentication and also delegation were take care. I am sure they are working.

    Is there any thing else i can try to setup my link server.

    thanks

  • January 13, 2009 at 7:01 am

    #925834

    I donno why my link server setup is not working. I still doubt my code here to setup.

    EXEC sp_addlinkedserver @server='DevRevenue',

    @srvproduct='',

    @provider='SQLNCLI',

    @datasrc='',

    @provstr='Integrated Security=SSPI'

    I used this code from the forum where KERBEROS settings was explained.

    Can someone explain each and every bit of this code so that I can find out if i have used the correct one in my environment.

  • January 13, 2009 at 7:29 am

    #925871

    Can you ask the domain administrator (whoever runs the AD there) to see if the two SQL Servers are enabled for Kerberos delegation? It's a setting at the AD level, not one within SQL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 46 total)

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