January 13, 2009 at 8:36 am
And use Kerbtray in your troubleshooting. That will show you if the tickets are being created correctly.
K. Brian Kelley
@kbriankelley
January 13, 2009 at 9:55 am
Yes, I checked with with windows admin he says all the SQL Servers are KERBEROS and Delegation is also enabled.
Myself also checked on all the servers with the following sql
select uses_self_credential as delegation
from sys.linked_logins as L, sys.servers as S
where S.server_id=L.server_id and S.name=N'DevRevenue'
The result gives me '1', I think that means delagation is enabled.
January 13, 2009 at 10:06 am
Have you done troubleshooting with KerbTray running on the client computer?
K. Brian Kelley
@kbriankelley
January 13, 2009 at 11:19 am
Kelly
Sorry am nto aware of "KerbTray". Can you please let me know how wud i troubleshoot with it.
thanks
January 13, 2009 at 11:26 am
http://letmegooglethatforyou.com/?q=Kerbtray
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
January 13, 2009 at 2:01 pm
Just following along on the thread.
When setting up the linked server.
provstr 'Integrated security=SSPI"
SQL SERVER1 (Use domain\tracey)
MYFIRSTDB
MYFIRSTTABLE
SQL SERVER2 (User domain\tracey)
(Here i set up linked server to go to SQL SERVER1 and use the provstr)
Is it at this stage it is using "Kerbos" or NTLM?
How are the credentials of domain\tracey passed from SQL SERVER2 to SQL SERVER1 (using? what protocol).
i cannot get this to work using DOMAIN\tracey only works if using sql login.
So im right with the other user here.
January 13, 2009 at 2:04 pm
How do you know if using kerbos ? and any more details on the kertray.exe
what is it to check for?
January 13, 2009 at 2:05 pm
TRACEY (1/13/2009)
Just following along on the thread.When setting up the linked server.
provstr 'Integrated security=SSPI"
SQL SERVER1 (Use domain\tracey)
MYFIRSTDB
MYFIRSTTABLE
SQL SERVER2 (User domain\tracey)
(Here i set up linked server to go to SQL SERVER1 and use the provstr)
Is it at this stage it is using "Kerbos" or NTLM?
How are the credentials of domain\tracey passed from SQL SERVER2 to SQL SERVER1 (using? what protocol).
i cannot get this to work using DOMAIN\tracey only works if using sql login.
So im right with the other user here.
You still need to make sure your servers have SPN setup correctly
AND on AD they are marked as "trust this account for delegation".
* Noel
January 13, 2009 at 2:15 pm
You still need to make sure your servers have SPN (This is ?)
setup correctly
AND on AD they are marked as "trust this account for delegation".
(On AD the user domain\tracey would be configured on AD for trust this account for delegation,
is that correct.
When using the Integrated Security=SSPI (Am i now using kerbos).
Cheers
January 13, 2009 at 3:33 pm
Found a great thread
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
select uses_self_credential as delegation
from sys.linked_logins as L, sys.servers as S
where S.server_id=L.server_id
and S.name=N'LinkedServer'
The resulting table should show the delegation column is “1”.
This is working.
When i run the linked query using domain\tracey
select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid
This is giving me SQL which shouldn't be SQL protocol.
So i guess there is something missing on AD ? not set up.
January 14, 2009 at 7:45 am
Found a great thread
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
Tracy
Even i followed the same thread to configure my linked server, but it didnt work not sure where am i going wrong.
January 14, 2009 at 8:08 am
Yes i can get the linked sever to work using sql login just fine, but not with domain/tracey.
Lets hope someone else comes on here for some direction.
Did you get anything from this
select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid
I keep getting SQL - it could be that i just used Studio on the server itself.
execute as login = 'domain/tracey'
Then i call select * from [linkedserver].dbname.tablename
revert; ---this set it back to super user sa.
January 14, 2009 at 9:34 am
If KERBEORS is not working , can i acheive that through sql login.
ALready i havea sql login as Link between the servers but i just gave read access to all the db's for that Link Login but some users may also need wrote permissions on some db's through Link Login, how wud i acheive that in sql 2005.
January 14, 2009 at 9:47 am
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.
Maybe this isn't authentication related. Have you tried bracketing your database name in the query to the linked server?
SELECT * FROM LinkServerRev.[PA_Rev].dbo.Joblist
January 14, 2009 at 12:20 pm
Brackets go around like this
select * from [LinkedServer].DBNAME.SCHEMANAME.TABLENAME
I get the following:
OLE DB provider "SQLNCLI" for linked server "LinkedServer" returned message "Login timeout expired".
On linked server i have
Provider SQL Natvie Client
DataSource Servername
Provider String Integrated Security=SSPI
(Be made using the login current security content) - Local Login (DOMAIN/Tracey)
set to impersonate.
As soon as i untick impersonate and use sql login the above select statement works.
So i wonder what im missing .........
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply