ASP.net windows authentication With SQL Server
Hello Experts
I currently have an access project connected to SQL Server 2005 with Windows authentication done via my Domain Controler. Each login in SQL Server is in the form mydomain/user and has a specific role in SQL Server. These roles determine what the user can access in the database. This works just fine SQL server recognizes who make the connection and grants the appropriate permissions according to there role. However we are planning to shift our front end from Access Project to ASP.net pages.
I need step by step instructions how how to mimic this authentication in asp.net. So basicly the user will login to windows like normal and open our asp.net website. They will then automaticly be authenticated into SQL Server via there windows login using the domainname/username login that exists in SQL Server and not using NT Authority/Network Service, So that the Membership Roles in SQL Server for that user will be Preserved.
Thank you for your time and I look forward to your answers.
I currently have an access project connected to SQL Server 2005 with Windows authentication done via my Domain Controler. Each login in SQL Server is in the form mydomain/user and has a specific role in SQL Server. These roles determine what the user can access in the database. This works just fine SQL server recognizes who make the connection and grants the appropriate permissions according to there role. However we are planning to shift our front end from Access Project to ASP.net pages.
I need step by step instructions how how to mimic this authentication in asp.net. So basicly the user will login to windows like normal and open our asp.net website. They will then automaticly be authenticated into SQL Server via there windows login using the domainname/username login that exists in SQL Server and not using NT Authority/Network Service, So that the Membership Roles in SQL Server for that user will be Preserved.
Thank you for your time and I look forward to your answers.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is your SQL server and IIS on the same machine?
If not then you will have to use delegation:
http://support.microsoft.com/default.aspx?scid=kb;en-us;810572
http://www.thescripts.com/forum/thread340876.html
http://www.pluralsight.com/blogs/keith/archive/2004/07/08/1586.aspx
If not then you will have to use delegation:
http://support.microsoft.com/default.aspx?scid=kb;en-us;810572
http://www.thescripts.com/forum/thread340876.html
http://www.pluralsight.com/blogs/keith/archive/2004/07/08/1586.aspx
ASKER
slado2
the Website hosted by my IIS is on the same machine as my SQL Server, however my domain controler is on a seperate machine. hope that helps
the Website hosted by my IIS is on the same machine as my SQL Server, however my domain controler is on a seperate machine. hope that helps
ASKER
Hi slado2:
Thank you for your comments. I ended up contacting Microsoft and found out the problem was that my IIS Controller was not properly set up. For those who are interested there is the complete solution from Microsoft:
To connect to SQL Server from an ASP.NET web application, you need to do two things:
1. Configure Web Site to use integrated windows authentication with impersonation. (requires changes in IIS)
2. Use windows authentication in forming the connection string for SQL Server. (requires change in code inside the website)
Here are the detailed steps on how you can achieve the above two steps:
1. Configuring Web Site to use windows authentication:
For this again you will have to do two things:
a) Configuring IIS: This will enable IIS to collect windows identity from end user and pass it further to ASP.NET process.
Start Internet Information Services (IIS).
Right-click your application's virtual directory, and then click Properties.
Click the Directory Security tab.
Under Anonymous access and authentication control, click Edit.
Make sure the Anonymous access check box is not selected and that Integrated Windows authentication is the only selected check box.
b) Modifying Web.Config: For every web request that hits web application, ASP.NET process creates a new thread. So for each web request, a separate thread is assigned. This modification in web.config will enable ASP.NET process to pass the windows identity further to the thread that is handling web request for the user.
Use the following configuration in Web.Config
...
<system.web>
...
<authentication mode="Windows"/>
<identity impersonate="true"/>
...
</system.web>
...
2. Modifying connection string in code: (By now, ASP.NET thread runs in context of the authenticated user, so we just need to tell SQL Server that please look for the identity of the thread hitting on you. We do that by adding a security attribute in the connection string).
Typically connection string is as given below:
SQLConnection con= new SQLConnection();
Con.ConnectionString= @ Data Source=MyMachine;Initial Catalog=MyDatabase;Integra
Thank you for your comments. I ended up contacting Microsoft and found out the problem was that my IIS Controller was not properly set up. For those who are interested there is the complete solution from Microsoft:
To connect to SQL Server from an ASP.NET web application, you need to do two things:
1. Configure Web Site to use integrated windows authentication with impersonation. (requires changes in IIS)
2. Use windows authentication in forming the connection string for SQL Server. (requires change in code inside the website)
Here are the detailed steps on how you can achieve the above two steps:
1. Configuring Web Site to use windows authentication:
For this again you will have to do two things:
a) Configuring IIS: This will enable IIS to collect windows identity from end user and pass it further to ASP.NET process.
Start Internet Information Services (IIS).
Right-click your application's virtual directory, and then click Properties.
Click the Directory Security tab.
Under Anonymous access and authentication control, click Edit.
Make sure the Anonymous access check box is not selected and that Integrated Windows authentication is the only selected check box.
b) Modifying Web.Config: For every web request that hits web application, ASP.NET process creates a new thread. So for each web request, a separate thread is assigned. This modification in web.config will enable ASP.NET process to pass the windows identity further to the thread that is handling web request for the user.
Use the following configuration in Web.Config
...
<system.web>
...
<authentication mode="Windows"/>
<identity impersonate="true"/>
...
</system.web>
...
2. Modifying connection string in code: (By now, ASP.NET thread runs in context of the authenticated user, so we just need to tell SQL Server that please look for the identity of the thread hitting on you. We do that by adding a security attribute in the connection string).
Typically connection string is as given below:
SQLConnection con= new SQLConnection();
Con.ConnectionString= @ Data Source=MyMachine;Initial Catalog=MyDatabase;Integra
ASKER
I have all ready tried this, this is my connection string in web.config
<add name="SQLConnectionString2
providerName="System.Data.
And
<authentication mode="Windows" />
<identity impersonate ="true "/>
however when ever i connect without having nt atuhority/network service as a member of my database it fails, but with it, it succeds. Also i have a trigger setup in one of my table where if a new record is inserted (or a record is modified) it inserts User_ID() (from SQL Server) to my "added by" field in the table. I use this to check who made the entry, and it has allways been nt authority/ network service (when it is added as a database user.), instead of mydomain/user (who is also an authorized user for the database.). It is very crucial that the right user be identified because when we insert data we use triggers in SQL Server to insert the user information. Also different users have different roles and thus have different permissions to the database. We also use these roles to determine which forms(or in the case of ASP pages)/reports the users can access. Please let me know how to fix this and if you could please provide some more details, it will be very much appreciated.
PS my server hosting SQL Server & the server hosting my intranet (or internet we havent desided which to use) are currently the same.