Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2002
    Posts
    15

    Angry Unanswered: Login failed for user '(null)'. Active Directory issue?

    Here is the situation...

    I have numerous SQL Servers. All SQL2K w/SP2 and security patches. All report the same version number. All are set to use mixed mode security, although all our applications/users connect using NT authentication. All our servers reside in the same domain as do the users.

    Our client machines are a mix of WIN2K and NT4. Our apps connect using either ADO, OLEDB, or ODBC. The servers are setup to listen on TCP, Shared Memory, and Named Pipes. Workstations are setup to communicate on TCP and NP.

    Our primary production database server is NT4. We are looking to upgrade it to WIN2K. In order to do this, I must "failover" to the backup system during the upgrade (our business is 24/7). The backup system, being a mirror, is also NT4. When trying to failover this weekend, we got the:

    Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection

    ...error message from the apps while trying to connect to the backup system. The exact same apps work fine against production or any of our other systems. Meanwhile, I am able to connect to the backup server from Query Analyzer and EM with no problems.

    Other things to note...

    * The error seems to only occur when connecting from Win2K workstations. And some Win2K workstations also get the error under EM and QA.

    * We are in the middle of a transition from NT4 to Win2K domain controllers. Could this be impacting our authentication?

    * The backup server was recently a Win2K machine that was later downgraded to NT4. Could that be causing an issue somewhere?

    I believe this problem to actually be outside of SQL Server itself, but it doesn't make it any less my problem nonetheless.

    Thoughts?

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Is this a web or client/server application ? Have you noticed that this failure occurs with odbc or oledb connections ?

  3. #3
    Join Date
    Aug 2002
    Posts
    15
    Originally posted by rnealejr
    Is this a web or client/server application ? Have you noticed that this failure occurs with odbc or oledb connections ?
    Client/server (C++, VB, and VB.NET front ends)

    Failure occurs for ADO, OLEDB, and ODBC connections.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Have you tried the ip address of the backup server for the data source in your connection string ? Are you using the trusted_connection parameter in the connection string ?

  5. #5
    Join Date
    Aug 2002
    Posts
    15
    Originally posted by rnealejr
    Have you tried the ip address of the backup server for the data source in your connection string ? Are you using the trusted_connection parameter in the connection string ?
    Yes on the trusted connection setting.

    We have not used the IP address in the connection string. I will have the guys try that. We have been simply using the machine name (I think that uses NETBEUI to translate out). They are currently testing using the full DNS entry machine.domain.com to see if that helps.

    UPDATE -

    If the server is set to ONLY use NP incoming connections, the clients connect successfully. When TCP listener is activated on the server, the clients then try to use TCP (that is their primary) and connection fails. Which is more efficient on the network, NP or TCP?

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    TCP is faster. your situation is very similar to what was happening in 6.5 world, when if using Windows Authentication TCP/IP connection attempt resulted in the error you posted.

  7. #7
    Join Date
    Aug 2002
    Posts
    15
    FINAL UPDATE - GOT IT WORKING...

    After painstaking researching and trial and error, the result boils down to Kerberos security vs NTLM security (somewhat outside the scope of SQL Server, but not really). When SQL Server is installed on a Windows 2000 server in an Active Directory environment, an SPN (ServicePrincipleName) is created, if the installation is run under an account other than the LocalSystem account. If thte SPN becomes invalid (i.e. the user account used to run the MSSQL service changes), the SPN must be deleted and recreated.

    SQL Clients (Win2K and later) will first hit the Active Directory and look for an SPN for a service and the target machine. If an SPN is found, the connection will use Kerberos security. Kerberos is not valid for an NT server, only 2000. Hence the problems connecting with my system. If an SPN is not found, the connection defaults to NTLM security, which NT supports.

    By removing the previously created SPN (when the machine was originally Win2000), the connection was successful. SPNs can be manually e dited using the SetSPN.EXE included with the Windows 2000 Resource Kit.

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Great job - thanks for posting such a detailed description. This almost seems like a failure on the cleanup side of ad... Since this is happening in the background, you would hope that ms would delete the spn in this situation.

  9. #9
    Join Date
    Aug 2002
    Posts
    15
    Originally posted by rnealejr
    Great job - thanks for posting such a detailed description. This almost seems like a failure on the cleanup side of ad... Since this is happening in the background, you would hope that ms would delete the spn in this situation.
    Except... The machine originally was Win2000 and SQL2000. That is when the SPNs in AD were created. The machine was later installed with NT4. No uninstall was done on Win2000 of SQL. NT4 doesn't know it needs to clean up the AD stuff as it is not aware of ADs existence. It would be like downgrading a database from SQL2000 to SQL7. SQL7 can't undo the changes 2000 did as it is not aware of such a thing as 2000. The only way to downgrade would be if 2000 managed it.

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    But the spns are managed by ad - not each individual machine. When you removed the w2k server from the ad, this should have been cleaned up by ad.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •