• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6140
  • Last Modified:

Tracing the error 8009030C (SEC_E_LOGON_DENIED) back to source ?

I am getting the following error on my MS SQL box :-

SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.5.10]

After doing some research it appears that this code relates to :-

"Code 8009030C is SEC_E_LOGON_DENIED, which means the username/password did not match. Are you sure the username and password is correct".

My problem is that the webservers that access this database use many connection strings. I would like to know which connection string is causing this error but do not know how to trace exactly where the failing request is coming from.

Can anyone help ?
0
Oxfam_Australia
Asked:
Oxfam_Australia
  • 5
  • 5
1 Solution
 
patriktCommented:
You can use SQL Profiler and catch "Audit Login Failed" events. It will show you details of failed login as Client machine, username. By this you should identify failing application and its connection.

But I have to say that it is ussualy application's task to trace connection error and write it down to some logfile.

Patrik
0
 
aetelCommented:
Hello Oxfam,

You are not alone with this problem, as a matter of fact there are quite a few questions/answers right here on Expert Exchange about this. Search the knowledge database with keywords 'SSPI handshake' !

One of the important sources for explanation is Microsoft Knowledgebase, article KB811889.
Another good one is the question by 'cip', read that one, too!

It boils down to an authentication process, which involves 'delegation' and the correct definition of the SPN (Server Principal Name)!
The authentication can be NTLM vs. Kerberos, and the channel can be Named Pipes vs. TCP/IP, and, but of course, all ties in to the correct DNS record of the server .

I would like to hear about your success solving this problem.

Best regards,

aetel
0
 
Oxfam_AustraliaAuthor Commented:
Thank you for your responses. We ran SQL profiler, as recommended,  and did some further testing and found out something interesting. The SQL server is assessed by a linux server (apache) and a Windows 2003 server (IIS 6). We created a PHP file that called a stored procedure on the SQL server. The PHP file contains a loop so the stroed procedure is called every 10 seconds. We then ran this file on the two different webservers. Our IIS Server has PHP 5 installed.

The PHP file on the linux box caused no SSPI on the SQL server but the PHP file on Windows boxed caused many SSPI errors.

Any ideas where to from here ?



0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
patriktCommented:
Yes. Linux box will probably never do SSPI because it is not part of NTLM or Kerberos authentication. I guess that linux will use SQL authentication in itsa connection.
So you have two test cases:
1) Change your IIS to run on SQL auth.
2) Invertigate what is user account your IIS application is running under and find why it has problem authenticating in windows (NTLM, Kerberos) mode.
0
 
Oxfam_AustraliaAuthor Commented:
Thank you for your answers. Could you please give more detail on "Change the way IIS runs on SQL Auth".

At the moment the PHP connection string that I use on the IIS webserver looks like this :-

$objConnection = mssql_connect($strServerName, $strUsername, $strPassword) or die("Could not connect to " . $strDatabaseName);
mssql_select_db($strDatabaseName) or die("Could not select database");

The user here exists only on the SQL box, not on the IIS box.

The site running this code has only the Enable Anonymous Access box checked in Authentication Methods. Where do I need to make the changes ?

Thanks again.
0
 
Oxfam_AustraliaAuthor Commented:
I have been working through the document referred to by aetel. It appears that my DNS is fine and my SQl Server is running as LocalSystem account.

Both my servers are in their own Workgroups. It appears that I have to set up a trust  relationship between the two boxes. Do this mean that I will first have to put each machine in their own domain and the setup a trust or can I do this between workgroups ?

Thanks
0
 
patriktCommented:
No, you need nothing as trust or any other domain security as long as you use SQL authentication.
SQL authentication is totaly independent on system security.

1) Check if connecting user exists on SQL and that it is SQL authentication type.
2) Chekc that you connect with SQL authentication. I know nothing about PHP conneection, but there can be option to use SSPI or SQL auth. Check PHP help of mssql_connect().
0
 
Oxfam_AustraliaAuthor Commented:
I checked the syntax. There is a command to use SSPI, which I obviously do not want to. There is also a line in php.ini for mssql.secure_connection, if this is set to yes it assumes a trusted connection.

Many of the forums say to simple use SQL authentication just use the syntax I have above.

Any ideas what syntax I would use to connect to SQL server using SQL Authentication and produce an SSPI error. It is worth noting that the string does still connect and everything works it is just that I loads of SSPI errors in the application log and in the SQl log.




0
 
patriktCommented:
If it all uses sql auth it has to be some other piece which is causing SSPI errors.
When you run SQL Profiler and trace Logon Failure events do you see something meaningfull in Application Name column? Compare it with other succesfull logins (Autir Logon Success) of your PHP application.
0
 
Oxfam_AustraliaAuthor Commented:
The PHP on IIS runs every 10 seconds and causes an SSPI error every 10 seconds. The PHP code is exactly the same as the code that runs on our Linux webserver that does not cause the SSPI error. There appears to be something at the OS level which is causing the difference.




0
 
patriktCommented:
Did you checked SQL Profiler if it is your PHP application or other component whcih is caousing these SSPI errors? You can find application name in exents data.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now