locked
Error: 18456, Severity: 14, State: 6. RRS feed

  • Question

  • I am using SQL Server 2005 with Mixed Authentication. I would like to login to SQL Server using a domain account, but I don't want to use SSPI. Is this possible? Here is my scenario:

    The SQL Server is running on my development machine that is in the Domain 'LAB'. There are two users('LAB\admin01, LAB\admin02) in the domain. I added the user LAB\admin02 to SQL server and gave the account permission to access the required database. Now, I login to the machine as LAB\admin01 and start a .net console application. I want to access the SQL database as LAB\admin02. The console application uses a username and password for the connection string and so I supply admin02's user name and password.

    I am expecting SQL Server to allow the authentication to succeed, but the error message I get in the log is State 6 which translates to "attempt to use a Windows login name with SQL Authentication" . Why does this not work?   Why doesn't SQL server take my username password and authenticate with the domain controller, if it knows that it is not a sql login? I just want to know, why the authentication mechanism is designed to work this way.

    I am guessing that I can probably impersonate the LAB\admin02 in code before accessing the database, but that is not what I want to do. The larger issue I am trying to solve is to provide a connection string to the ASP.NET sql session state provider: I am required to use a domain account as per our security policies, and ASP.NET does not have impersonation enabled and so it runs as a Network Account. I want the session state provider to use domain account but I don't know how I can make just the session state provider to run as one domain user and the rest of the asp.net application run without impersonation?

    Here is my console application:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.SqlClient;
    using System.Diagnostics;

    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder()
                {
                    ApplicationName = "Orion",
                    DataSource = "(local)",
                    UserID = @"lab\admin02",
                    Password = "password",
                    InitialCatalog = "aspstate2"
                };

                string query = "select count(1) from ASPStateTempApplications";

                using (SqlConnection connection = new SqlConnection(builder.ToString()))
                {
                    connection.Open();  //exception thrown.

                    SqlCommand command = new SqlCommand(query, connection); 
                    int numberOfRows = (int) command.ExecuteScalar();

                }

            }
        }
    }
    Thursday, June 25, 2009 10:24 PM

Answers

  • You can't do this.  First off, Windows Authentication uses SSPI and there isn't any way around it.  This is part of the authentication infrastructure.

    If you specify a user name and password, SQL Server treats that as a standard SQL Server login.  When you use your Windows credentials to connect, SQL Server simply sends a request to the Windows security API and asks it to validate your Windows token.  It has no interaction with a Windows login/password and no code at all to do something like that.  If you need your application to connect to SQL Server under a different Windows credential, then it is up to you to either run your application under that security context or have your application impersonate the Windows account.  SQL Server has no capability to do this.
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    • Marked as answer by Raghu Dodda Friday, June 26, 2009 3:41 AM
    Friday, June 26, 2009 2:48 AM