locked
SQL Server security question RRS feed

  • Question

  • Hello All,

    I have a question regarding security.

    Right now in our company we just have sa account and everyone is using the same sa account to access sql server.We just dont want to happen this anymore.We decided to apply some security to our server.This is my first project as  DBA.

    I have decided to create 2 user groups and assign members for each group.Group 1 (IT group)members should have individual logins and passwords(sql server authentication) and they will be having full access to the server and database.Group 2 (non IT) members should also have individual logins and passwords and they will just have read access to the database.As I cant get rid of sa account I am just planning to change the sa password and that I am going to share only with administrators so that no one can use sa to access the server.

    Is this a good approach to secure sql server?...Please correct me if I am wrong...Any suggestions or feedback would greatly help

    Thanks

    • Moved by Tom Phillips Wednesday, March 28, 2012 8:09 PM Security question (From:SQL Server Database Engine)
    Wednesday, March 28, 2012 6:49 PM

Answers

  • yes you can do that,but don't give sa password any one and go with below liks i hope that will help you 

    http://blogs.msdn.com/b/sqlexpress/archive/2010/02/23/how-to-take-ownership-of-your-local-sql-server-2008-express.aspx

    http://www.sqlservercentral.com/blogs/brian_kelley/2009/04/21/sql-server-security-basics-logins-vs-users/

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

    http://www.networkworld.com/community/node/42290


    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    • Proposed as answer by LMU92 Wednesday, March 28, 2012 7:47 PM
    • Marked as answer by Maggie Luo Thursday, April 5, 2012 8:11 AM
    Wednesday, March 28, 2012 7:07 PM
  • I see that I responded to a similar thread, but this has a specific question or two.

    Why Windows Logins instead of SQL Server logins? And what steps should I take?  Well, here is one explanation which includes helpful tips:

    http://www.mssqltips.com/sqlservertip/1831/using-windows-groups-for-sql-server-logins-as-a-best-practice/

    SQL Server logins are not wicked, but they tend to be less secure (because of the implementation and all the possible points of exposure) than Windows logins.  Nothing is absolute, of course, and it is up to you to decide.

    All the best,
    RLF

    • Marked as answer by Maggie Luo Thursday, April 5, 2012 8:11 AM
    Thursday, March 29, 2012 8:49 PM
  • With that small organisation, you may not even had a domain, and thus not any active directory. And if you don't have a domain, Windows authentication does not work well, so in that case SQL Server authentication is a better choice. But if you have a domain, you should go for Windows authentication. That is not only more secure, it is also less hassle for users that don't have to specify a second set of username and password.

    Please keep in mind that when we answer your question, we only as much about your situation as you tell us, so we have to extrapolate from general experience.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Maggie Luo Thursday, April 5, 2012 8:11 AM
    Thursday, March 29, 2012 9:49 PM

All replies

  • yes you can do that,but don't give sa password any one and go with below liks i hope that will help you 

    http://blogs.msdn.com/b/sqlexpress/archive/2010/02/23/how-to-take-ownership-of-your-local-sql-server-2008-express.aspx

    http://www.sqlservercentral.com/blogs/brian_kelley/2009/04/21/sql-server-security-basics-logins-vs-users/

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

    http://www.networkworld.com/community/node/42290


    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    • Proposed as answer by LMU92 Wednesday, March 28, 2012 7:47 PM
    • Marked as answer by Maggie Luo Thursday, April 5, 2012 8:11 AM
    Wednesday, March 28, 2012 7:07 PM
  • I would suggest is that you instead use Windows authentication and use group memberhip in the AD to control permissions in SQL Server. This gives you less administration when people come and go. And then you can disable SQL Server authentication, so that sa cannot be used.

    It's a little funny when you say that non-IT people will only have read access to the database. How will they perform updates?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 28, 2012 9:13 PM
  • I'm not sure how carefully you were using the word groups. And you didn't mention SQL Server roles.

    Windows users go into Windows groups. Windows groups get SQL Server Windows Authentication logins.  Logins go into (SQL Server) server roles, and also get database users. Database users go into database roles. Permissions are best assigned to roles.

    This might sound complicated, but it's very close to what you already said. Database Engine Permission Basics 


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, March 28, 2012 10:30 PM
  • Non-IT people in our company they dont need to update anything in sql server.They just need read access to view the data but they are not allowed to make any changes.Ours is a small team.I need to add 5 users to have full access to the sql server and 2 users with limited access.Since it is a small group, Cant I just create logins for all the users as sql server authentication  and give permissions?..I am not that familiar with AD and adding windows users groups...If sql server authentication is not the best practice could anyone explain me the steps to be taken when windows authentication should be used.

    Thank You so much

    Thursday, March 29, 2012 2:06 PM
  • I see that I responded to a similar thread, but this has a specific question or two.

    Why Windows Logins instead of SQL Server logins? And what steps should I take?  Well, here is one explanation which includes helpful tips:

    http://www.mssqltips.com/sqlservertip/1831/using-windows-groups-for-sql-server-logins-as-a-best-practice/

    SQL Server logins are not wicked, but they tend to be less secure (because of the implementation and all the possible points of exposure) than Windows logins.  Nothing is absolute, of course, and it is up to you to decide.

    All the best,
    RLF

    • Marked as answer by Maggie Luo Thursday, April 5, 2012 8:11 AM
    Thursday, March 29, 2012 8:49 PM
  • With that small organisation, you may not even had a domain, and thus not any active directory. And if you don't have a domain, Windows authentication does not work well, so in that case SQL Server authentication is a better choice. But if you have a domain, you should go for Windows authentication. That is not only more secure, it is also less hassle for users that don't have to specify a second set of username and password.

    Please keep in mind that when we answer your question, we only as much about your situation as you tell us, so we have to extrapolate from general experience.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Maggie Luo Thursday, April 5, 2012 8:11 AM
    Thursday, March 29, 2012 9:49 PM