sa login for an application
-
Hello everyone
we use a particular application that has always had its' own dedicated SQL Server, but the databases are being moved to a central SQL Server (which has databases for multiple apps). In the past this application has always used the 'sa' login to access SQL Server.
my concern is that the application in question requires the use of the 'sa' login (as explained to me by the company which supports the application).
what options do I have for securing my 'sa' login to the whole sql server ?
I worried about compromising my database server just to make sure this one application can perform as needed.
-
Check why it requires SA login, are they using any specific functionality which requires SA access...
I don't think you can anything if you grant SA access other than auding the login what it is doing...if something goes wrong to prove it application which screwedup...
MohammedU
Microsoft SQL Server MVP -
First, I would inform the vendor selling the application that you have begun to find a replacement for their product (even if you can't). Application developers (and their managers) need to be encouraged to develop applications that run with less permission than system administrator. Really, the only way to encourage that is through their pocketbook.
Second, can the application run as database owner? This is somewhat better than running as a system administrator. You may want to disable ownership chaining. Can the application run as a different system admin user than sa? This is only moderately better than running as sa.
Third, until you can run the application as a lower priviledge user (at least as low as database owner), you need to run the application on its own server or server instance. You can connect to the application database from other applications using linked servers or using some client access (ODBC, ADO.Net, ADO, etc).
Russel Loski, MCSE Business Intelligence, Data Platform
-
I'm tired of lazy arrogant application developers who won't consider how to run with less permission than system administrator. I just had a major blow up with an internal developer who always wants elevated permission.
One of the first posts of the new year, and obviously done without reading Steve's article on Rules of Conduct (http://www.sqlservercentral.com/columnists/sjones/2767.asp), especially item #3.
Mark -
Mark, you got me there!!
I corrected my original post.My point was simply this: it is far less expensive to software development companies to use the sa password. In fact, one vendor I have dealt with uses a blank sa password. This is the easiest of all.
Unless paying customers insist that these companies build simple security into their products, there is little motivation to change.
Russel Loski, MCSE Business Intelligence, Data Platform
-
I'd recommend you try it with a regular login, maybe db_writer/reader in the db and see what happens. I've had a few of these applications "require" SA, but only one did. Great Plains used to let the accountants create logins and needed SA for this. It could have been security admin, but I didn't like that either.
So we built a workaround. An email form that accountants could use to "request" a login. We built it, they added it to GP, ignored the error, and were fine.
-
You may also need to check with the vendor to see if the 'sa' login & password are held in a table, .ini file, configuration file, etc. If it is hard-coded and you can change it, do so. If not, at least you would have found a large security risk.
We had a couple of applications that recommended using the 'sa' account, but I refused to install them that way. It seems that dbo is the least amount of privilege needed for the applications to work but at least they're not using a sysadmin account. I suggest that you at least try to change it....
-
just my 2ct
- also keep in mind that many of the "technical" sales people even don't know their app runs just fine with a non-sa sqlaccount.
- you can try to implement a least-privilaged account, but you'd have to grant it read/insert/update/delete/execute for all views/tables/procs/functions
Part of this can be done by db_reader/db_writer groupmembership.
- If it actualy creates persistant objects during the application's lifetime, maybe even make it dbo, if the previous option did not work.
- if it uses xp_cmdshell, implement a proxiaccount for it.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply