Sql-server – recommended level of security for a production database

logsSecuritysql serversql-server-2005

I have a production database on our local server. Currently there are two many logins to the database, about 20, half of them are windows authenticated and the other rest are SQL Server authenticated. I would really like to keep the logins minimum, so that I know who has accesses the database while also maintain security, but also want to keep safety factor, like keep one windows authenticated user so the we have access to the db in case no one has password. My question are

  1. What is the recommended security level of production server? Should it be Windows authenticated only or SQL Server authenticated?

  2. How many logins should one keep? one or more?

  3. Should the default sa login be disabled, since it can be a security threat?

Any other security risk, I need to be aware of? Our application as well as database both sits on the same local server.

Best Answer

  1. Windows Authentication should be chosen over SQL Server Auth. Windows Auth is inherently more secure, as it's controlled by the OS. Granted, there are times when you won't be able to get around a SQL Server Auth principal because of a third party application, or whatever the surrounding circumstances are.
  2. You should have the amount of respective logins for each logical principal that can connect or could be audited. In other words, if every application uses the same login, and your auditing captures login name, it'll be virtually useless.
  3. You should disable the sa account as it can be a security threat. The reason behind this is because it is extremely well-known login name for SQL Server instances. It takes out a lot of the guessing game for hackers. Disable this account, but ensure there are other appropriate principals with sysadmin privileges.

As for other security measures, they are far and vast. Our own member, mrdenny, has written a book on this very subject. A few pointers are you'll want to keep a tight sand box on your principals. In other words, give only permissions that the principal will need in order to perform the functionality, and no more.

Related Question