September 4, 2006 at 6:25 am
This is really annoying me now! I have 2 SQL servers out of around 50 that I have trouble connecting to, the problem being I cannot create an ODBC connection using integrated security over TCP/IP. As soon as I change to named pipes I can connect fine. The 2 error messages I get are:
Login failed for user ''. The user is not associated with a trusted SQL Server connection
SSPI handshake failed with error code 0x80090302 while establishing a connection with integrated security; the connection has been closed.
I have gone over everything - the users have permissions, the server allows remote connections, it's setup in mixed mode, no firewalls, ports are configured corrcetly - all the basic troubleshooting steps seem to be fine. Also I can actually connect over TCP/IP from some machines - the difference seems to be that XP clients are okay, 2000 clients are not.
It's driving me mad - any help appreciated.
cheers
September 4, 2006 at 9:22 am
It's an AD/Domain issue. I've had similar issues that have driven me crazy as well. Search on SPN and try setting one for the machine. I've usually seen this with the service account for some readon.
If you change the service account to admin, does it work?
August 27, 2007 at 2:08 pm
Where do I check the settings for this? I'm having the same issue, and I it's causing some services to not be able to start.
Rich
August 28, 2007 at 4:23 pm
It seems that the client that cannot connect is something on the server itself. I assume it's something in IIS, but everything looks fine. The reportserver in IIS is set to windows authentication.
My exact error is:
SSPI handshake failed with error code 0x80090302 while establishing a connection with integrated security; the connection has been closed. [CLIENT: X.X.X.X]
Source MSSQL$EXPRESS
Where the client IP is the server's IP address. I can't think of any other client on this machine that would be logging in wrong.
I'm at a loss
August 30, 2007 at 11:07 am
First thing you need to do is download the setspn utility (link below):
Install it on your computer (or server). Next you'll need a Domain Admin account to use (unless you are a Domain Admin you cannot use setspn for this type of AD updates). Open up a DOS command window change directory to C:\Program Files\Resource Kit (the setspn installation default directory).
Then enter the following comand:
setspn -L servername
Your results may look something like this:
MSSQLSvc/servername.xxx.organization.org:1433
HOST/servername
HOST/servername.xxx.organization.org
Next enter the following commands:
setspn -D MSSQLSvc/servername.xxx.organization.org servername
setspn -D HOST/servername servername
setspn -D HOST/servername.xxx.organization.org servername
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply