none
SQL 2008 R2 and Webserver - How to secure connection from web.config and dbconf.php?

    Question

  • Hi,

    today we found that both Windows server (IIS 8) and Linux (Apache 2.) are running PHP 4.4.9 for a specific application and they have a sql user which is a 'sysadmin' inside the MS-SQL server and its user/password is store in a file (pure text).

    I found the information bellow regarding SQL 2000, should we consider that recomendations regarding to avoid to use SQLLogin´s and prefer to use WindowsLogin?

    How to use a WindowsLogin? Just changing the user that start the 'World Wide Publish Server' service??

    Can someone share thoughts?

    * our concern is not related with external visitor but with network administrators (a team from our company and also from external service company).

    ----

    "Windows authentication is the recommended way to connect to SQL Server because you avoid storing credentials in connection strings and you avoid passing passwords over the network to your database server. You can also use Microsoft Active Directory® directory service for unified account management and to enforce strong password policies. If you do need to use SQL authentication, then you need to protect stored credentials in your connection strings by encrypting the connection strings. In addition, you might need to secure the credentials as they are passed over the network from your application to the database server. Also, you should not use the sa account. Instead, use a least privileged account with a strong password."

    http://msdn.microsoft.com/en-us/library/ff648340.aspx


    • Edited by 9073241516 Saturday, December 28, 2013 1:59 AM update info
    Saturday, December 28, 2013 1:54 AM

Answers

  • I would say that the biggest concern is that the account is in the sysadmin role, and it should be investigated whether that is needed. It is not unlikely that it is sufficient that the user is only member of db_owner in the database. Even better, it suffices if the user is member of db_datareader and db_datawriter and/or EXEC permissions on stored procedures. It will take some testing amd investigation to find out. If db_datareader/writer/EXEC permission is not enough, there is reason to investigate whether the application can be modified.

    The way Windows authentication works is that you log into Windows with your user name and password. When you connect to SQL Server, Windows pass your login token and vouches for you. That is, at the time you connect to SQL Server you cannot specify a different Windows user. Or a Windows user if you are not logged into Windows at all.

    For the IIS Server, you could use Windows authentication. In this case you would grant access to the service account for IIS. I believe that this is typically local system, why you would grant access to the machine account DOMAIN\Server$. Which means that anyone with admin access to the machine can set up a service that logs into SQL Server. So you may not be able to keep your network admins out.

    For the Linux server, Windows authentication may not be even an option, unless Apache logs into Windows through Samba


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, December 28, 2013 9:51 AM
  • ...they have a sql user which is a 'sysadmin' inside the MS-SQL server and its user/password is store in a file (pure text)....

    Adding to what Erland said, it is quite brutal to leave passwords plaintext within configuration files.

    Under Windows, windows authentication makes it a little easier to prevent such scenarios

    other than that you really should encrypt those entries. On the how-to that's more an IIS topic.

    Similar thread: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ec594143-948a-4a2b-bd83-717bb981bf1f/how-to-connect-to-database-from-iis7-website?forum=sqlsecurity


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Saturday, December 28, 2013 5:47 PM

All replies

  • I would say that the biggest concern is that the account is in the sysadmin role, and it should be investigated whether that is needed. It is not unlikely that it is sufficient that the user is only member of db_owner in the database. Even better, it suffices if the user is member of db_datareader and db_datawriter and/or EXEC permissions on stored procedures. It will take some testing amd investigation to find out. If db_datareader/writer/EXEC permission is not enough, there is reason to investigate whether the application can be modified.

    The way Windows authentication works is that you log into Windows with your user name and password. When you connect to SQL Server, Windows pass your login token and vouches for you. That is, at the time you connect to SQL Server you cannot specify a different Windows user. Or a Windows user if you are not logged into Windows at all.

    For the IIS Server, you could use Windows authentication. In this case you would grant access to the service account for IIS. I believe that this is typically local system, why you would grant access to the machine account DOMAIN\Server$. Which means that anyone with admin access to the machine can set up a service that logs into SQL Server. So you may not be able to keep your network admins out.

    For the Linux server, Windows authentication may not be even an option, unless Apache logs into Windows through Samba


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, December 28, 2013 9:51 AM
  • ...they have a sql user which is a 'sysadmin' inside the MS-SQL server and its user/password is store in a file (pure text)....

    Adding to what Erland said, it is quite brutal to leave passwords plaintext within configuration files.

    Under Windows, windows authentication makes it a little easier to prevent such scenarios

    other than that you really should encrypt those entries. On the how-to that's more an IIS topic.

    Similar thread: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ec594143-948a-4a2b-bd83-717bb981bf1f/how-to-connect-to-database-from-iis7-website?forum=sqlsecurity


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

    Saturday, December 28, 2013 5:47 PM