November 7, 2008 at 6:15 am
Where are you running the query from? If it is your desktop, you have the classic "double hop". Try logging onto to one of the servers and run the query.
Just an FYI, I've never had much luck with Kerberos and delegation. Even had MSFT trying to get it to work for us and they couldn't.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
November 7, 2008 at 7:16 am
You are absolutely right. If I login on the Server A it works. Now, if you can tell me how to make this to work from my computer it will be great.
Thank you,
November 7, 2008 at 7:22 am
See if these help out.
http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx
http://technet.microsoft.com/en-us/library/cc786325.aspx
Make sure the user the service is running under, the server, and the user accessing are not marked as Sensitive.
Also you will not see the Delegation Tab in AD until a service is registered.
There's a little utility in the server resource kit called SETSPN that is good for troubleshooting / verifying the needed endpoints.
ADSI Edit is the easiest tool to use to edit SPN's (which you have to be a domain admin to create SPNs)
Turn on kerberos logging in the registry
I also use a reg key for MaxPacketSize to force Kerberos to use TCP instead of UDP
I was never able to find a good article that covered everything, especially when setting up an app on W2008. There are some differences base on the OS/SP level, version of SQL server being used, and service. Most notably - at least to me - some require a port specification and some do not.
For SQL 2005, the endpoints under the user account SQL server would look like this:
MSSQLSvc/Server Name:Port (Default is 1433)
MSSQLSvc/Server Fully Qualified Domain Name:Port
Analysis Services looks like this:
MSOLAPSvc.3/Server Name
MSOLAPSvc.3/Server Fully Qualified Domain Name
So unless you are a Domain Admin, it's very likely you'll need to find one. Especially if you run your services under a Domain User Account.
Greg E
November 7, 2008 at 7:24 am
I can't help you with the double hop. I'm sure there are many of us who are frustrated with that limitation. If you have some really sharp network people, you may be able to get delegation to work, but, as I said even MSFT couldn't do it for us.
I can suggest 2 things you may be able to use. First, the simplest way I can think of is to set this up in a job on Server A or B and start the job from your desktop with SSMS, SQLCMD, etc. The second would be to create an SSIS package and you can run that on your desktop.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
November 7, 2008 at 7:24 am
It sounds like you might have a delegation issue. Read this for instructions on how to set up:
http://msdn.microsoft.com/en-us/library/aa905162(SQL.80).aspx
The setspn commands can be a bit tricky to get right depending on how your domain is set up. Also, I believe you need to run the setspn command for both servers. You will need to restart the SQL Service on both after running setspn too.
November 7, 2008 at 7:38 am
Thank you for trying to help me. I will continue wit this on monday when our system administrators will be here and I will let you know if I find a solution.
Thank you again,
November 7, 2008 at 7:47 am
I spent quite a bit of time trying to get Kerberos to work with linked servers and gave up and used sql accounts. However, I found these articles later that I think would be very useful in troubleshooting Kerberos:
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
Let us know if these help you out.
TC
November 7, 2008 at 7:56 am
Dejan,
Is it really necessary to get delegation to work? From my experience and from what I've read here and elsewhere, if there is a reasonable alternative, it does not seem to be worth the effort.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
November 7, 2008 at 7:57 am
One other thing to verify - that all machines are in the same AD domain / forest.
If not, you have to verify that there is a trust in place. And for forest to forest, the DCs would have to be at W2003 functional level or greater.
Just in case - took us awhile to figure this one out.
Greg E
November 7, 2008 at 8:06 am
Set up a sql login on servera and serverb with full sa rights and then use this login in the linked server...click on linked server and right click linked server and do use this be made with this security context and put in mynewsa login here.
Try that does it work.
I never been able to put in MYNTLOGIN\DOMAIN One it even tells you can't do it.
Also there may be something in the SQL Server Agent under proxies and you may have to add the NT\DOMAIN login in here....something with replication even though i had this NT\DOMAIN on both servers without adding this in here the replication didn't work can't remember the reason but worth
putting out here.
November 7, 2008 at 8:14 am
MG (11/7/2008)
Dejan,Is it really necessary to get delegation to work? From my experience and from what I've read here and elsewhere, if there is a reasonable alternative, it does not seem to be worth the effort.
We have some intranet applications that require Windows Authentication, running on multiple servers. The first time setting it up can be a bit confusing. After that, it's much easier.
The real advantage of Kerberos is the ability to carry user context / security across the boxes. This can be real important with web applications and cubes.
So if you need 'per user' level security, and wish to leverage AD security, that would have a lot of influence in determining the need.
Thanks,
Greg E
November 28, 2008 at 2:01 pm
Hello,
The problem is solved. Finally, it works :). When i setup delegation for the service account ("Trust this user for delegation to any service (Kerberos only)") the problem was solved.
Thanks to all for trying to help me.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply