Stop Devs from using Service account in prod

Question

Stop Devs from using Service account in prod

WI-DBA

SSCrazy

Points: 2110
More actions
July 15, 2010 at 12:14 pm

#387364

Developers have been losing production privs for quite awhile.(which they earned) They didn't go quietly, but due to this they have started using the production service accounts to access the data and do whatever they want to. (within the security of their service account). Since I can't take away giving them the passwords to their service accounts so they can setup their applications - I need another avenue of attack.
Fiddling with how to stop the madness. My easy way out is using the Governor and catch the service account and the application name (or hostname) and put it into a pool with no resources available. This is like using a shotgun to swat a mosquito.
I thought perhaps AUDIT would be useful but I am not seeing enough in the area of connection properties in the AUDIT facets (maybe I am missing it)
Next looks like using extended events and the Audit Login Event Class - my level of knowledge here is really low - but if its the right hammer, I will dig in. (Have @SQLSarg's whitepaper already)
Anybody been down this road and have any wisdom?
Thanks
Cheers
http://twitter.com/widba
http://widba.blogspot.com/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

As a developer, I would love to have access to the production box, so I can "do it myself", unfortunately I don't and for good reason, especially now the Sarbannes Oxley has come into play. We have services that are running in Production. As we deploy to Production, we have deployment instructions. In that we state, once this service is setup, use this ID. We never know what the passwords are and don't have access to them. Our developers only get data_reader access to the production dbs. You could change the passwords and have the developers go through the proper channels to get changes made to production. If they "have to do it themselves", then maybe their documentation is where it should be for the app. Just my 2 cents

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 2

take a look at this thread about logon triggers...

you could make a trigger like the example that refuses a connection if they are using the specific SUSER_NAME() of the account and using SSMS, or not from the HOST_NAME() of the server itself

http://www.sqlservercentral.com/Forums/FindPost953191.aspx

maybe that accoutn should only be used by the server itself form the server itself, the possiblities get bigger as you understand the complexities.

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!

WI-DBA SSCrazy Points: 2110 More actions · Answer 3

Logon trigger, of course! Danke.

I concur on the pwd management. In other place, a network admin created and encrypted the service account passwords and the devs never knew it.

It is not feasible here yet, because the builds are done so horribly. But thats a future policy fix for sure.

Cheers
http://twitter.com/widba
http://widba.blogspot.com/