January 25, 2012 at 5:50 pm
Have 2 production ERP systems. One runs on SQL 2005 enterprise and does not support windows authentication, so all users have sql logins only. The other system runs on SQL 2008 enterprise R2 and all user logins are controlled at the proxy/app server level and logins aren’t set up in sql.
These systems use linked servers and share the same ‘sa’ database password which is years old and weak. I have been unable to change the password because a few of our developers have the db connection string with sa password hard coded into VB6 and .Net apps that users run from their machines. It’s a touchy subject because they are change-resistant, grumpy programmers that don’t want to update all their apps.
With support from management, I am going to force the sa password change, but would also like get the developers on their own accounts first.
I do have mixed (sql and windows auth) mode set on both sql servers. I have a couple ideas, but I wanted to bounce this off of you all first and follow best practices. Anyone ever been in this situation? Do you have suggestions? Should I just set up a “development” account for all their apps?
January 26, 2012 at 8:00 am
In both cases I'd set up a developer role or roles in SQL Server and assign developer accounts to those role(s) as needed. If possible I would use windows authentication for individual logins instead of having to manage SQL accounts.
You'll get a lot of opinions on this, but, if there is a DBA, the developers shouldn't need sysadmin access on any servers and definitely should not have it on the production servers.
For a development environment I wouldn't have a major issue with sysadmin for developers, but it isn't absolutely necessary. You should be able to create roles for them that allow them to do what they need to do, which is mostly CREATE and ALTER objects, beyond the normal CRUD operations. There may be cases where they need to create LOGIN's and USER's, but I'd prefer to work with them in this case.
For production developers should NEVER have sysadmin access and in the majority of cases, don't need anything beyond the normal application users rights. Any problems encountered in production should be able to be duplicated in a development or test environment.
For linked servers I typically setup a remote access/linked server login that I use that has minimal rights on the databases. Typically read only access as I don't usually to INSERT/UPDATE/DELETE across linked servers. I'd definitely stop using sa here.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 26, 2012 at 8:21 am
I agree with jack. But I have few questions, answers to which might change my course of action.
•How many applications are there in total?
•Are these home (local) developed applications (no consultant / contactor involved)?
•Impacted users if the application goes down (suppose you changes ‘sa’ without warning)?
•Is it one server or many servers?
•PROD server?
If applications are less (2-3), developed locally, total impacted users are few developers then they have no good reason to argue with you, even if you change ‘sa’ password without informing anyone. DBAs own PROD servers. None except DBAs (if a group) should know the ‘sa’ password of the PROD environment.
January 26, 2012 at 9:52 am
Dev (1/26/2012)
I agree with jack. But I have few questions, answers to which might change my course of action.•How many applications are there in total?
•Are these home (local) developed applications (no consultant / contactor involved)?
•Impacted users if the application goes down (suppose you changes ‘sa’ without warning)?
•Is it one server or many servers?
•PROD server?
If applications are less (2-3), developed locally, total impacted users are few developers then they have no good reason to argue with you, even if you change ‘sa’ password without informing anyone. DBAs own PROD servers. None except DBAs (if a group) should know the ‘sa’ password of the PROD environment.
• My first step is getting a list from the developers just to see what all will be affected. Only a couple are cooperating.
• These are locally developed.
• Hundreds of users would be affected if they go down.
• About 15 servers; 5-6 are prod boxes
I plan to create an alternate account and have them gradually pull their stuff onto the new account. Once they THINK they have updated everything, I want to change the sa password and see what breaks.
January 26, 2012 at 10:05 am
Jack Corbett (1/26/2012)
In both cases I'd set up a developer role or roles in SQL Server and assign developer accounts to those role(s) as needed. If possible I would use windows authentication for individual logins instead of having to manage SQL accounts.For linked servers I typically setup a remote access/linked server login that I use that has minimal rights on the databases. Typically read only access as I don't usually to INSERT/UPDATE/DELETE across linked servers. I'd definitely stop using sa here.
Do you recommend setting up the DEV role and assigning the individual user accounts for developers to that role? Or assign app1, app2, linkedServer accounts to the role?
There's linked servers set up between all my sql servers and they all share the sa account and we do INSERT/UPDATE/DELETE across linked servers.
This was all in place before my time. Just want to get everything in order.
January 26, 2012 at 10:41 am
SkyBox (1/26/2012)
Do you recommend setting up the DEV role and assigning the individual user accounts for developers to that role? Or assign app1, app2, linkedServer accounts to the role?
You have 2 separate things here. Developers who need DDL permissions and Apps that need CRUD permissions (ideally only EXECUTE on procedures, but I'm guessing that isn't an option at this point).
So for the developers I'd have a developer role, maybe per application, and assign the developers explicitly to that role or roles as needed.
For the applications I'd have roles for each application, and, if possible, assign individual AD users or even AD Roles to the SQL Server role(s) setup for each application. There could be multiple roles per application. FOr example in an HR application you might have a role for those who need to see/edit employee personal info (name, address, etc...) and another role that edits work information (Title, rate, etc...)
There's linked servers set up between all my sql servers and they all share the sa account and we do INSERT/UPDATE/DELETE across linked servers.
Depending on how granular you want to be with linked servers you could even go to the point where you have a linked server per application and a specific user for that linked server and application. I have typically used a single SQL Server user for linked servers and then given rights within databases as needed. I'm not a big fan of linked servers and when I do use them I try to be calling sp's on the remote server instead of doing direct table access. I've found it to be faster and less prone to problems.
I'd also suggest evaluating how linked servers are used to see if there is a better way to handle these needs. Replication, service broker, and even connecting 2 both servers within the application might be better solutions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 26, 2012 at 11:28 am
I agree with Jack on most points but personally I'd use AD groups, not individual users, for security assignment. This way all the security associations exist at the AD level (and thus, not your concern), are maintained according to existing mechanisms (thus no work for those who's concern it is), and you aren't constantly fiddling with umpteen users worth of security.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 26, 2012 at 11:46 am
Evil Kraig F (1/26/2012)
I agree with Jack on most points but personally I'd use AD groups, not individual users, for security assignment. This way all the security associations exist at the AD level (and thus, not your concern), are maintained according to existing mechanisms (thus no work for those who's concern it is), and you aren't constantly fiddling with umpteen users worth of security.
I agree with the use of AD Groups, but in my experience, sometimes you can't get the AD folks to setup the groups that you need for the level of granularity you need at the DB level. IDeally that's the best way to do it though.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 26, 2012 at 11:50 am
That's great information Jack. It's difficult to scope this without a list of their apps - based on the list I have so far, there will be about 25-40 apps and at least 50 SQL processes/routines/jobs. Don't know that I can dedicate enough time to this at the moment for an ideal setup. Unfortunately, security always ranks last here and being short on staff doesn't help.
Given my situation, what's the fastest way to lock down this sa account, but still have the ability to add/build additional security as time permits? Just need to take the first steps in the right direction.
January 26, 2012 at 12:08 pm
SkyBox (1/26/2012)
That's great information Jack. It's difficult to scope this without a list of their apps - based on the list I have so far, there will be about 25-40 apps and at least 50 SQL processes/routines/jobs. Don't know that I can dedicate enough time to this at the moment for an ideal setup. Unfortunately, security always ranks last here and being short on staff doesn't help.Given my situation, what's the fastest way to lock down this sa account, but still have the ability to add/build additional security as time permits? Just need to take the first steps in the right direction.
I'd do this:
1. linked servers off sa account.
2. apps off sa account
3. Setup specific developer roles in application db's without sa rights.
3. change sa password
4. devs get specific rights and groups without sysadmin
I think you have to do it that order or else the devs will never stop using sa. Yes, there will be issues when you make the cutover in each case.
You can fine tune security once you get off the sa account, but I honestly doubt you'll ever get to that. I highly recommend getting security right now. You'll have to do it one app at a time. If you have a development/test environment I'd just start there by changing the sa password and work through there. Script everything, don't just use the GUI. You want something for each app that you can just run in production.
If you have buy-in from management you need to convince them that if you don't do it right this time it probably won't get the priority to be fixed later. In reality that's how they got where they are now. They didn't have time to do security right the first time.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 27, 2012 at 4:06 am
Thanks for all the advice. Good call on the scripting - actually have a good start on it already.
February 5, 2012 at 3:24 am
SkyBox (1/25/2012)
coded into VB6
God that shows how behind the times they are, have you also explained that Ronald Reagan is no longer president 😀
SkyBox (1/25/2012)
It’s a touchy subject because they are change-resistant, grumpy programmers that don’t want to update all their apps.
Ask them for the admin password for their source safe and see if they'll give it to you :Whistling:
SkyBox (1/25/2012)
With support from management, I am going to force the sa password change, but would also like get the developers on their own accounts first.I do have mixed (sql and windows auth) mode set on both sql servers. I have a couple ideas, but I wanted to bounce this off of you all first and follow best practices. Anyone ever been in this situation? Do you have suggestions? Should I just set up a “development” account for all their apps?
With management backing you're half way there, just be sure to get a clear policy detailed and signed off by management.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 21, 2012 at 1:49 pm
Finally setting up and testing this.
What about end users that run apps locally? If using windows authentication (SSPI), how can I allow users or AD domain groups access to db's- within the apps, without giving them direct access.
Some of these end user apps update records, so I can't risk setting up the AD account in SQL and giving direct access to the db's.
Was considering storing the "application" logins and passwords in a secure config file. The developers could then use a DLL to look up the username and password within their apps.
This is definitely not ideal, but I could gain control of the sa account. Any better ideas?
February 21, 2012 at 2:54 pm
SkyBox (2/21/2012)
Finally setting up and testing this.What about end users that run apps locally? If using windows authentication (SSPI), how can I allow users or AD domain groups access to db's- within the apps, without giving them direct access.
Some of these end user apps update records, so I can't risk setting up the AD account in SQL and giving direct access to the db's.
Was considering storing the "application" logins and passwords in a secure config file. The developers could then use a DLL to look up the username and password within their apps.
This is definitely not ideal, but I could gain control of the sa account. Any better ideas?
There's nothing wrong with an app using Windows Authentication. As a matter of fact that's the best way to do it. You just need to be sure to only grant those rights needed to users. Really you should use AD groups and then create appropriate database roles.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 21, 2012 at 3:20 pm
Jack Corbett (2/21/2012)
There's nothing wrong with an app using Windows Authentication. As a matter of fact that's the best way to do it. You just need to be sure to only grant those rights needed to users. Really you should use AD groups and then create appropriate database roles.
Using win auth was my original plan, but If I assign AD groups to the the appropriate db roles, wouldn't an end user be able to connect to SQL db's via access, excel, or another program using windows credentials?
End users cannot access db's using windows credentials currently - it's all controlled at the proxy/application server level for our ERP system.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply