CODEWITHSUNDEEP
asp.netsqlactive-directory
russds
russds

Reputation: 875

Connecting to SQL Server using active directory credentials

I thought this would be simple, but I can't seem to figure it out. I want to have a c# form asking for username and password. I want to then validate the user, by asking SQL if that user account can login to the SQL Database. I've tried a sort of rudimentary method, of just putting the username and password in the connection string, but that didn't work, I got an error message "user so-and-so doesn't have access" i'm assuming because the password isn't carrying over correctly. This is what I have:

Web.config:

<connectionStrings>
  <add name="connectionname" 
     connectionString="Data Source=myserver;Initial Catalog=mydb;Integrated Security=SSPI;" 
     providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
   <identity impersonate="true" />
   ....

My Code behind:

 try
 {
    SqlConnection con = new SqlConnection();
    con.ConnectionString = "Data Source=myserver;Initial Catalog=mydb;User ID=" + useridTextBox.Text + ";Password=" + passwordTextBox.Text;
    con.Open();
    con.Close();
    //successful login
    validDBLogin = true;
    pageMsg.Text = "Successful AD and DB login";
  }
  catch (Exception err) //if error Opening, catch the error, and display
  {
    pageMsg.Text = "No access to the Database";
    pageMsgAdditional.Text = err.Message.ToString();
    validDBLogin = false;
   }

There must be a way to do this? My goal is to have authentication done with SQL. If a user wants access, I give them access to the database, and that's all i have to do. I also want to have the security integrated in this way so I can trace which user ran which sql query. (Not sure if it makes a difference, but we are using Active Directory for these account, and so far no trouble with that).

Thanks in advance!

Upvotes: 1

Views: 5849

Answers (2)

JourneyThroughCode
JourneyThroughCode

Reputation: 429

Passing username password for active directory Id is not possible. It always uses the Id of the application pool under which the website is running.

Upvotes: 2

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

You have misunderstood the SSPI integration in SQL Server.

Connection String 1 (SSPI):

Data Source=myserver;Initial Catalog=mydb;Integrated Security=SSPI;

This tells SQL Server to check the credentials of the user that owns the process that is trying to connect. In this case, the ASP.Net Application Pool Identity is the context of the "Active Directory user".

Connection String 2 (user/password):

Data Source=myserver;Initial Catalog=mydb;User ID=xxxx;Password=yyyy;

This tells SQL Server to authenticate the User ID "xxxx" as a SQL Server login.

This does not present a domain user "xxxx" for SQL Server to authenticate. Not even if you use the format "Domain\xxxx". You just cannot.

Upvotes: 4

Related Questions