SQL Connection String - passing Windows Security user profile

  • 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!!!

  • Websites use the application pool in IIS to access the SQL Server unless you enable impersonation

  • 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?

  • 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.

  • You MAY have to enable windows authentication and take off anonymous users in the website configuration on IIS.

  • 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/

  • The client absolutely knows the account logged in….. System.Security.Principal.WindowsIdentity

    My web developer run all our internal application on windows authentication.

  • 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.

  • Neal Sivley (8/18/2011)


    The client absolutely knows the account logged in….. System.Security.Principal.WindowsIdentity

    My 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?

  • /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/

  • 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.

  • 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.

  • 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