Home / server / Questions / 2127
Accepted
JoshBerke
Asked: 2009-05-01 20:06:21 +0800 CST

What is considered more secure for sql server security

  • 772

When setting up security on SQL Server 2005 / 2008. What is the more secure option?

  • Windows Authentication Mode Only
  • Mixed Authentication

Does it matter if the server will be accessed by many desktop clients (thick clients) or if a handful of web servers will be accessing it?

Edit

And why is it more secure? Using Windows Authentication does mean we can avoid putting the connection string in the configuration file which is a plus.

Windows Authentication also allows us to control who gets access based on their NT credentials, which I would think is ideal when your clients connect directly to the server.

One thing I always wonder however, is how useful NT authentication is when all clients go through a proxy such as web service.

sql-server sql-server-2005 sql-server-2008 security

4 Answers

  1. Best Answer

    Windows authentication is considered more secure. Here's why:

    • You can use Kerberos authentication. The Kerberos protocol has a timestamp which prevents replay attacks. It also allows the client to verify the identity of the server using a trusted 3rd party (in Active Directory's implementation, this is the DC).
    • It allows for a single security source: Active Directory. Therefore, once you shutdown an account in Active Directory, it is disabled everywhere.
    • In SQL Server versions prior to SQL Server 2005, the login packet was not automatically encrypted. The way the password was passed on the network was easily decrypted since we're talking about high and low order bit flipping and an XOR operation.
    • There is the fact that you don't have to put the password in the connection string, but there are other ways around this. For instance, encrypting username/password and storing it in the registry, and then building the connection string at runtime.
    • Logins to the SQL Server will be tracked in the Security event log, along with other Windows logins, if such audit settings are turned on (and they should be). That means if you have log parsing/aggregation software for your servers, you don't have to parse out the application event log or the SQL Server logfile necessarily.

    With respect to where you have a single account from a web service, you still have all the same advantages that I've listed above.

    • 8

  2. Windows Authentication is more secure primarily because the username and password are not passed in the connection string. Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

    As opposed to Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

    • 5

  3. More secure is Windows Authentication Mode Only assuming you have good network security in general.

    Could you give a little more detail on your current setup and needs?

    • 1

  4. Basically, Windows authentication is the recommended authentication. It is secure by theory, that is if you have a good network security already in place.

    • 1