Password check in/check out functionality within SQL SERVER.
-
Hi Guys
I was wondering whether Ms provide some kind of mechanism for managing SQL passwords, the scenario here is that, there are support analysts who sometimes need to diagnose application problems which run on SQL server, in order for them to be able to do this effectively, they need to have the SA password, whilst from a security perspective, this is wrong, but from a support/customer perspective, they need to get things working and they don’t care about what security arrangements are in place. The ideal scenario will be to have a system whereby when support staff need access to the SA password an email alert goes out informing staff that the password is currently being used by a specific user, they startup the application which provides them with the SA password, they then do what they need to do, tell the application that they have finished and the application changes the password back again.
The whole thing doesn’t sound difficult to do, lets say we use an application like powershell to write a cmdlet.
But I want to know the thoughts of other users on the forum on this matter, or how they have tackled similar problems.
Thanks.
-
Just an idea, if you have not already thought of it.
How about creating a copy of the database on a non production server,
then you can give them whatever access they need, yet not be concerned with security of your production database(s). Once they figure out the problem and come up with some corrective action that requires changes to the production server let them come to you and discuss same. You can then implement their suggestions or reject them as required. Sort of give the support analysts a "sandbox" to play in.
-
You could create another account with the same rights as sa and instead of changing the password, just enable/disable that account when needed.
-
I agree with bitbucket on forcing them to test on a sandbox. I presume you don't have to abide by any SOX or other auditing rules if that is the current process. If you did the sandbox would be the only solution. The issue with giving them the SA account is they can then shoehorn themselves as SA unless you have an ever vigilant monitoring. We had one box that was UAT which we gave SA to one person. I looked a few weeks later and there were a half dozen new SA's. Another server we inherited the developer went and added themselves not just to SA but every privileged server role.
I have not seen any solutions like you mention although a slight twist on the enable/disable process is you could have a predetermined AD group which you could add/remove which analyst was in it. Not sure if that would help meet your needs.
-
Ran across this blog post by Jack Corbet titled "Maintaining Security and Performance using Stored Procedures" at:
Which details a method of using the EXECUTE AS feature, and this may be very applicable to what you desire to do.
-
I don't think you should let them use the SA account. Give them another account with the rights they need. Consultants always want to have sysadmin rigthts and you will be surprise, but most of the time they don't need the rigths.
As for your question I don't know about such a solution from Microsoft.
-
Maybe I'm not explaining myself properly, all I really want is for a way to audit the administrative login that may be used by support staff, they know what the username/password is, but I dont want them using it for day to day work, but only for real admin purposes when the DBA isnt available for support. Will SQL 2008 auditing help here or some 3rd party password management application, the idea is, you invoke a process, the process gives u an admin username/password to connect to the database with an alert will be sent out stating that user xxx is now using the username/password, after doing any work the password is changed again automatically to what the user does not know, if you need it next time, you ask for it, an alert gets triggered, it gets changed again (password) after its expiry period i.e say 12 hrs.
-
There's nothing like this that I know of. You could probably write something that would spawn them a session with a password, or would change the pwd, email it to them, and log it. You could even have a process that after xx minutes it goes and changes the password no matter what.
However a constant running SQL Trace, to a folder that the support people don't have rights to, would accomplish something similar.
-
why not use a windows group that has admin rights? you give them a network login, set the password to expire in three days or whatever, and you are done;
they log in, can do whatever they need to, and if they come back again in six months, you can update their windows login so they can work again.
that way i'd think they have the rights they need without letting them have the sa password.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell has a good idea but you need to make sure that the support people don't change the password expiry and change the SA pwd itself.
"Keep Trying"
-
Lowell (10/21/2009)
why not use a windows group that has admin rights? you give them a network login, set the password to expire in three days or whatever, and you are done;they log in, can do whatever they need to, and if they come back again in six months, you can update their windows login so they can work again.
that way i'd think they have the rights they need without letting them have the sa password.
I did think about that, and its definitely an option. i.e create them a windows account which has admin rights, have auditing in place so that when they login using this account, an alert gets triggered, so we at least know what they are doing something, but the ideal scenario is to have a username/password, SQL 2008 has an enable/facility. The reason why I say username/password is because, lets face it, if you have customers complaining bitterly about a problem and you want to resolve, the last thing you need is to have to start logging into a new Os session so as to use your higher peviledge account, you just want to get to solving the problem straight away.
so thats why I thought a username/password solution will be quick, I just need a way to manage the systsem, its like having a ship and saying, in in emergency break the glass for the fire exit door keys. obviously afer the fire has been sorted, you replace your fire door key again, in this case a new password.
-
You can use "RunAs" and launch SSMS this way to connect with a higher privilege account. We used to do that in the W2K days.
-
Steve Jones - Editor (10/22/2009)
You can use "RunAs" and launch SSMS this way to connect with a higher privilege account. We used to do that in the W2K days.Thats one way, then grant the user the permission to be able to impersonate, the problem here is that if a user wants to run profiler, it doesnt allow you to do so using impersonate.
-
If that's the case, I'd suggest they keep two sessions on their workstation, one with a higher privilege account. Handle issues administratively. Don't allow Outlook, etc. in the high privilege session.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply