asp.net windows authentication to sql server

I can not seem to get sql server to recognize my credentials.

Asp.net recognizes me when I login but when I execute a sql command I get a login failed message.

The IIS server and SQL server are on different machines.

There are other applications the IIS server which are able to authenticate to the sql server. I believe the Active Directory settings are correct. I am investigating what I am doing differently.

I must be missing something.

I check the IIS settings
The web config is set to impersonate.

Below is the relevant information. If anyone has any idea as to what I missed or am doing wrong I would appreciate some help.

IIS Settings:

‘Integrated Windows authentication’ is checked
‘Enable anonymous access” is not checked

Web Config

<authentication mode="Windows"/>
<identity impersonate="true"/>
<authorization>
   <deny users="?" />   
</authorization>

Page_Load Code:

Dim winId As IIdentity = HttpContext.Current.User.Identity
TextBoxMessage.Text = winId.Name + Environment.NewLine
Dim cnn As SqlClient.SqlConnection

Try
   Dim sql As String = "*****"

   cnn = New SqlClient.SqlConnection("Data Source=*****;Initial Catalog=****;Integrated Security=True")
   cnn.Open()

   Dim cmd As New SqlClient.SqlCommand(sql, cnn)
   cmd.ExecuteNonQuery()
   cnn.Close()

Catch ex As Exception
   TextBoxMessage.Text += ex.Message
   cnn.Close()
End Try

Output:

DomainUserName

Contents hide

Answers:

Method 1

Method 2

Method 3

Method 4

Method 5

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

This is key = The IIS server and SQL server are on different machines. You’re facing the classic double hop issue. Think of it in this way:

You access the web application under your credentials
IIS in this case has to present who you say you are to the SQL Server
IIS says, “Why hello SQL Server, I’m passing on Tony’s kerberos creds, they are legit”
SQL Server says, “Hmmm, IIS I’m not sure I trust you, I need proof that you are trusted to present these credentials to me”.

Thus once you enable delegation as mentioned by Remus, your SQL Server will trust the credentials your IIS server is presenting on your behalf.

In terms of security, under delegation, it would be wise to choose:

Trust this computer for delegation for specified services only | Use Kerberos only | and then underneath the “Services to which this account can present delegated credentials” make sure you explicitly set only the server/port you need.

Method 2

You also need to enable constrained delegation:

Method 3

Are your ISS server and SQL Server running on the same machine?

If not, Active Directory has to be configured to allow your IIS server to impersonate your accout towards the SQL Server. See How To: Use Protocol Transition and Constrained Delegation in ASP.NET 2.0.

Method 4

Long story short: IIS server should have “trusted for delegation” checkbox checked in Active Directory.

Method 5

You need to set you credentials in Application Pool.
– Open IIS
– Select Application Pools
– Enter the name, select .Net framework version and click OK
– Select the new added application pool and click on Advanced Settings..
– In the Process Model section click on Identity – choose custom account and enter the AD username, password, confirm password and click OK
– Select your application and in the Basic settings choose your application pool just created.

Hope this helps

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating