Is this a Kerberos issue? And if so any suggestions on how to fix it?

Question

Is this a Kerberos issue? And if so any suggestions on how to fix it?

samalex

SSCertifiable

Points: 5429
More actions
November 20, 2013 at 8:00 am

#300677

We've started testing DNS Aliasing to our SQL Servers, and on one new server we setup a DNS alias SQLSALES pointing back to the server. So to log into the server we use SQLSALES\SALES to connect to that instance. All seems to be working fine until I setup the maintenance plan for backups. When I create the Maintenance Plan while connected to SQLSALES\SALES from SSMS it uses SQLSALES\SALES as the server name in the Local Connection with Integrated Security, and when the maintenance plan runs we get "SSPI handshake failed with error" then "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.". When I change SQLSALES to the actual server name it works fine, so would this be a kerberos issue? And if so any suggestions on how to fix this?
I've read lots of Kerberos docs on Microsoft's site, but admittedly that area isn't my forte so I'm unsure how to modify the SPN's to correct this. Any suggestions?
Thanks,
Sam

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

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

samalex SSCertifiable Points: 5429 More actions · Answer 1

Just a quick follow-up on this. I've read through Brian Kelley's great article about SQL Server and Kerberos - http://www.sqlservercentral.com/articles/Security/65169/ - and from this I created SPN's for the domain and SQL Service accounts on the server, plus I changed the Instance port to Static and used that port. That looks correct, but I'm still unable to run SSIS scripts that use the DNS Alias name in the connection string with integrated security. I know the domain account is correct because using the server name instead of the DNS name works fine.

Thanks for any insight.

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 2

When you run cliconfg.exe, do you find an alias for your sql server there?

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

samalex SSCertifiable Points: 5429 More actions · Answer 3

IgorMi (11/21/2013)
When you run cliconfg.exe, do you find an alias for your sql server there?

We're using DNS Aliasing just to the server and not SQL Aliasing to the Instance, so would cliconfg.exe show a DNS Alias? Our Instance names are fairly static when we move databases between servers, so DNS Aliasing seems to be the better option for our environment.

I did run this, and how our SSIS scripts are working when we reference the DNS alias, but Reporting Services isn't working.

setspn -S MSSQLSvc/SQLSALES:13200 [Domain]\SQLSERVICEUSER

setspn -S MSSQLSvc/SQLSALES:SALES [Domain]\SQLSERVICEUSER

This article on TechNet seems to cover the SSRS part rather well -

http://blogs.technet.com/b/rob/archive/2011/11/23/enabling-kerberos-authentication-for-reporting-services.aspx

I'm testing this today, so hopefully this will get SSRS going as well. I'd appreciate any feedback from you or anyone else who may have suggestions on SSRS and Kerberos.

Thanks.

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 4

Not sure if you are running SSRS on the same machine as SQL, or another.

Verify that it is trusted for delegation.

And reboot the machines when you make changes.

Kerbtray is good to help troubleshoot.

And DelConfig can be helpful.