March 28, 2012 at 1:45 pm
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
March 28, 2012 at 2:14 pm
I would use NT Groups and NT Logins. When users are created, they get added to the appropriate groups in AD and have the appropriate access provided by assigning appropriate permissions to the groups in SQL Server.
Simplifies the administration to one place. If you need a new group, it is created in AD, you assign the appropriate permissions in SQL Server to that new group, then users can be added to that group when needed.
March 28, 2012 at 2:16 pm
I second Lynn. I prefer to use NT users and Groups when possible.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 28, 2012 at 2:22 pm
Thank you sor your suggestions.
I would like to know what will be the bottleneck if we use sql server suthentication?
March 28, 2012 at 2:23 pm
I did use some SQL Logins at a previous employer but those were limited to the DBA group to get access to the SQL Server systems when connecting from home over a VPN connection. Couldn't use our NT logins then.
Everything else was NT Logins or NT Groups. Well, except for the application connections that used SQL logins. But those were limited to those applications, users didn't use them directly.
March 28, 2012 at 2:55 pm
I agree with everything that's been said so far in terms of using NT Groups instead of SQL Logins. One item that was not explicitly addressed that you mentioned is the use of the sa account, I disable that login on all instances where allowable. In my opinion the sa login is not suitable even for administrator-use. For your admin peers, add their NT Logins, or better yet an NT group containing their NT Logins, to the sysadmin Fixed Server Role. If you must use a SQL login for access (as in Lynn's example for access from home or an application-service account) create an appropriately-named SQL Login for that specific purpose and add it to the appropriate Fixed Server Role.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2012 at 4:01 pm
opc.three (3/28/2012)
I agree with everything that's been said so far in terms of using NT Groups instead of SQL Logins. One item that was not explicitly addressed that you mentioned is the use of the sa account, I disable that login on all instances where allowable. In my opinion the sa login is not suitable even for administrator-use. For your admin peers, add their NT Logins, or better yet an NT group containing their NT Logins, to the sysadmin Fixed Server Role. If you must use a SQL login for access (as in Lynn's example for access from home or an application-service account) create an appropriately-named SQL Login for that specific purpose and add it to the appropriate Fixed Server Role.
I keep the sa login. Before I delete the Builtin\Administrators group I ensure that I have at least three ways to access the server as a sysadmin, and sa is one. I restrict who has access to this account. For the servers I was responsible for at a previous employer, I was the only one who knew the sa password. I had it written down, in a sealed envelop that was placed in a secure location in case something happened to me, like getting hit by the proverbial bus or leaving the organization.
If someone needed it, they had to go to my manager to get it. No one ever did, but if they had, I would have changed it once I found out it had been used.
March 29, 2012 at 8:10 am
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
March 29, 2012 at 8:16 am
Lynn Pettis (3/28/2012)
opc.three (3/28/2012)
I agree with everything that's been said so far in terms of using NT Groups instead of SQL Logins. One item that was not explicitly addressed that you mentioned is the use of the sa account, I disable that login on all instances where allowable. In my opinion the sa login is not suitable even for administrator-use. For your admin peers, add their NT Logins, or better yet an NT group containing their NT Logins, to the sysadmin Fixed Server Role. If you must use a SQL login for access (as in Lynn's example for access from home or an application-service account) create an appropriately-named SQL Login for that specific purpose and add it to the appropriate Fixed Server Role.I keep the sa login. Before I delete the Builtin\Administrators group I ensure that I have at least three ways to access the server as a sysadmin, and sa is one. I restrict who has access to this account. For the servers I was responsible for at a previous employer, I was the only one who knew the sa password. I had it written down, in a sealed envelop that was placed in a secure location in case something happened to me, like getting hit by the proverbial bus or leaving the organization.
If someone needed it, they had to go to my manager to get it. No one ever did, but if they had, I would have changed it once I found out it had been used.
I guess I do not ever worry because I know I always have this method in my back pocket, and so does everyone else: Get Back into SQL Server After You've Locked Yourself Out[/url]
I use the sa account for database ownership and Agent Job ownership, which works fine even when the account is disabled. I just cannot come up with a good reason to leave sa enabled.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 30, 2012 at 7:02 am
If it helps, here is the URL to a script I wrote to do a "Instance Security Audit Documentation" script. It will print in normal report format, or you can change a variable to make it actually do the scripts to set the permissions. Shows permissions for each database. Also shows access at the server level (first thing in the report), showing who is assigned to each fixed-server role.
http://www.sqlservercentral.com/Forums/Topic1226870-359-1.aspx#bm1230153
Can help fine-tune it, and document it when finished.
I, too, use the SA password, but I'm the only one that knows it. It is written in an envelope in my desk, locked up. I use my Windows login to have access to the servers, but I agree with the previous poster that you should have multiple, secure ways to access the server. Don't put all your eggs in one basket. Just make sure each is as secure as possible. One thing to watch is not letting others have local admin rights on the server itself. Among other issues, they would have access to any backups on the server.
March 30, 2012 at 7:49 am
If someone needs sysadmin privs their Windows Login gets it via an NT Group, or I make them a SQL Login if required by the circumstances. If I can help it though, no one logs into my instances as sa, ever. When I have my way only Windows Authentication is enabled on the instance, making sa useless. Much more often though SQL Authentication is enabled, and in that case while sa is disabled I will still have multiple ways to get into the instance if needed. The "in case of emergency break glass"-method in my earlier post is the last resort. Different strokes I guess...I like to have multiple ways to get in too, but am feeling a little left out because I do not have a sealed envelope somewhere with a password in it :hehe:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply