August 18, 2011 at 7:10 am
Hello
Our work is finally taking the plunge and developing intranet applications rather then desktop. Therefore, although I can do some coding, the code for server app thing is relatively new to me.
I have developed one of our applications whereas it connects to a SQL 2008 server database and brings back search results for the user. Everything was working fine, as I developed on my local machine and tested. But, once I migrated to our "staging" area on the server, I no longer have access to the Sql Database.
What is happening is this:
I had been using a SQL connection string
"Data Source="MyDataSource; Initial Catalog=MYCATALOG; Integrated Security=SSPI"
Our work uses a Windows Security log in, where all users log in to their desktop with a security NY user name and password.
When developing on my local server, this connection worked fine as it was able to control SQL table access by passing my user name and password to the database to verify access.
Now that I have migrated to a server (rather then local) for the front end of the application, I am getting denied access using this connection. I am now considered what appears to be a "general web user"
As a work around, I can create a general name and password and hard code it into the connection string, but this will allow EVERYONE to access the database, when it should be limited/restricted to those given access. Therefore it will force me to put the security on the application form rather then the SQL database and I don't like that approach.
Is there a way to get around this? A way to pass each users name and password from their desktop to the server, without creating text boxes and forcing them to log in every time? Our users HATE being forced to keep logging in for each and every application.
Thanks!!!
August 18, 2011 at 7:32 am
Websites use the application pool in IIS to access the SQL Server unless you enable impersonation
August 18, 2011 at 7:40 am
Neal Sivley (8/18/2011)
Websites use the application pool in IIS to access the SQL Server unless you enable impersonation
Thanks for the reply . . but more info please? I am pretty familiar with developing in Visual Studios, asp.net . . .but once we reach areas of server config and IIS . . I am so new it hurts.
1. Enable impersonations ??? Is that something that is done in the web.config file or within the IIS?
I personally only have access to the development tools, including web.config, but another team handles the IIS . or so I believe.
Anyway, I have used google a ga-billion (or is it googillion) times and found a lot of information about:
<identity impersonate="true"/>
Found lots of ways to make this work to "trick the system into believing it's always one user to connect" but I have not been able to find any relative information on HOW to pass each individual users credentials this way . . so they can log on as themselves .
Am I on the right path? Is this even possible?
August 18, 2011 at 7:43 am
Yes, you are on the right path. I am a DBA and only dable in web stuff but I think you just need to add the impersonation="true" to the webconfig and then it should pass Windows authentication thru.
August 18, 2011 at 7:43 am
You MAY have to enable windows authentication and take off anonymous users in the website configuration on IIS.
August 18, 2011 at 7:46 am
The app pool is the user that is logged in. The web server has no way to know the username of the client that is connecting. That would be a pretty massive security hole. The client makes a request from the web server. The web server does all the work to get the data and such and returns it to the client. You will be much better off to setup a db user for your website and connect to your database with those credentials.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 18, 2011 at 7:51 am
The client absolutely knows the account logged in….. System.Security.Principal.WindowsIdentity
My web developer run all our internal application on windows authentication.
August 18, 2011 at 7:52 am
Sean,
I understand and have done that many times for pervious apps (created a db user and used those credentials), however we are facing a different need for this application.
1. Our apps are all on secured intranet and not public internet. And the data being viewed is considered sensitive, therefore we need to control access and not grant everyone access by using a db user. Instead we use security groups and assign those groups access or not to the database.
Your way, we would need to assign those security groups to the actual intranet page that holds the application, rather than the server . .which, is possible . . however the intranet is controlled by another group other than myself, and we are working with them in hopes that we can continue to "bang out" needed applications without having to burden them with setting up security for each new page we create.
Unfortunately not being able to identify who the user is, will force us to use page security rather than DB security.
Sean Lange (8/18/2011)
The app pool is the user that is logged in. The web server has no way to know the username of the client that is connecting. That would be a pretty massive security hole. The client makes a request from the web server. The web server does all the work to get the data and such and returns it to the client. You will be much better off to setup a db user for your website and connect to your database with those credentials.
August 18, 2011 at 7:54 am
Neal Sivley (8/18/2011)
The client absolutely knows the account logged in….. System.Security.Principal.WindowsIdentityMy web developer run all our internal application on windows authentication.
Neal, I believe this may be what I am looking for . .
Can you give me an brief example of how to set that up so that up? How do I tell my connection string to use System.Security.Principal.WindowsIdentity, or how do I use that to pull a user id and password and then pass it in my connection string?
August 18, 2011 at 7:56 am
/facepalm
totally missed this was Intranet. That's what I get for responding for finishing my first cup of coffee. Yes internally you absolutely can get the current user. I am going to finish this cup and go get another...backs away slowly from his computer. :blush:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 18, 2011 at 8:01 am
System.Security.Principal.WindowsIdentity is a way to get the domain account of the user. This is use for when you want to audit. User “domain\user” made change “y” on 8/11/2011. You don’t need this to pass thru credential to the SQL server. I am pretty sure your issue will be that anonymous authentication on the website security configuration in ISS is enabled and that Windows Integration is disabled.
August 18, 2011 at 8:03 am
System.Security.Principal.WindowsIdentity is a way to get the domain account of the user. This is use for when you want to audit. User “domain\user” made change “y” on 8/11/2011. You don’t need this to pass thru credential to the SQL server. I am pretty sure your issue will be that anonymous authentication on the website security configuration in ISS is enabled and that Windows Integration is disabled.
August 18, 2011 at 9:01 am
Thanks for all of the input/information.
If I understand correctly I possibly need those two settings on IIS security changed . . which is another unit, than mine. I will set up a meeting to discuss with them, as they will probably know immediately what to do.
I had first misdiagnosed the issue, and was hoping it was something on my end, within the connection or application that I could do to pass the user info ..
Thanks to all who provided some good info!
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy