Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Managing security and Certificates Expand / Collapse
Author
Message
Posted Tuesday, May 12, 2009 10:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:10 AM
Points: 282, Visits: 1,090
Hi All

I have heard about the various enhancements to security configuration in SQL 2005, I was wondering how other people manage SQL logins in your respective environment, how would you manage users, I assume we all script this, what if a user needs access to update a table, would you simply open your script and change this to reflect the new change ?

Also, I have heard about certificates and was wondering how these are used and in what circumstances would you use certificates ?

I have a situation where an application needs SQL access to a database, this involves giving developers the application login and also the password as well, I was wondering whether are any alternatives to taking this approach ?

Also, how can I further lock SQL down to make sure that its only that application which can use that login, in effect stopping other people from using the login meant for the application.


Post #715288
Posted Tuesday, May 12, 2009 11:31 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
Dean Jones (5/12/2009)

I have a situation where an application needs SQL access to a database, this involves giving developers the application login and also the password as well, I was wondering whether are any alternatives to taking this approach ?

Also, how can I further lock SQL down to make sure that its only that application which can use that login, in effect stopping other people from using the login meant for the application.


I manage this by having different passwords in Production and Development for the application logins. Developers can develop using their own logins to the SQL Server for the most part. When it comes to testing, the app login is generally good to use so that the permissions are correctly scripted when migrating to production.

If you want to block a login from accessing SQL Server except from a specific application name, you can look at the following blog post I made last year:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/06/18/securing-production-servers-from-developers-with-ssms-and-application-usernames-passwords.aspx


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #715320
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse