sa Password

[riteshjain1982]

I was going through few SQL related forum and surpise to see many DBA faced problem knowing "sa" password.i dont have much idea about big Enterprise level application and organization,but just wondering if some application is accessing than isn't it somewhere in the application there must be a sa or atleast when app tries to make connection to DB it can be traced or these type of application hides sa password somewhere deep in the core of earth

[mendhak]

They are probably using other logins which have certain amounts of rights that allow them to do their jobs; sometimes they will need to go above and beyond the call an duty and then face the "I don't know the sa password" problem.

[Pasvorto]

First thing I did was to change the sa password to something i could remember.

[riteshjain1982]

First thing I did was to change the sa password to something i could remember.

Well that one can do only if only one application is accessing SQL server,but in case many other application is using same sa account i think it wont be all that feasible.

[mendhak]

Why would an application be using the sa login? Why don't you create separate logins for each application?

[riteshjain1982]

Why would an application be using the sa login? Why don't you create separate logins for each application?

In many small and mid scale organization i saw they stick with same SQL login for almost all their application ,and that why in my first post i mentioned isn't it possible to find out sa password from code it self !!

[mendhak]

I see what you're saying now. You have concerns that in some of the small applications you've seen, they're using the "sa" credentials. Well, yes, if it's used, then it would be possible in some way to find the login details for sa.

Why do they do it? Convenience? Force of habit? I don't really know.

[Pasvorto]

Well that one can do only if only one application is accessing SQL server,but in case many other application is using same sa account i think it wont be all that feasible.

I have 5 applicatiions all accessing the SQL Server. However, since I am also the programmer, I know the passwords and it is not a problem.

[riteshjain1982]

I have 5 applicatiions all accessing the SQL Server. However, since I am also the programmer, I know the passwords and it is not a problem.

Just wondering what will happen if all programmer working on an application for domain like finance,banking etc knows the SQL admin user id and password...

[mendhak]

It's a potential security threat. "Studies" that I cannot cite have shown that the biggest security threats are from within the organization itself. Therefore, there's nothing actually stopping a developer from logging in to the database and copying out the credit card information belonging to other people; or other sensitive data for that matter.

I think a good idea is to have separate environments, and to have the DBAs actually set the connection string values during deployment themselves.

[riteshjain1982]

mendhak,normally in this type of application where security is primary concern, where will you prefer to save connection string and what measure one can take to make sure connection string is really "secure"

[mendhak]

Encrypt the connection string or store it in the registry or store it in yet another database. In whatever you do, have your 'deployment' scenarios such that another person is performing the 'deployment' of the sensitive information.

Hmm, a more concrete example - developer creates application, specifies that the connection string information is located in the registry at so-and-so location. Deployer/admin deploys or xcopies the application over, looks at the registry path in config file, then goes and creates those registry settings and puts in the encrypted or unencrypted connection string information there. Developer does not have access to the testing/uat environment, so he will be unable to login to that box and have a look at the settings. This then shifts the threat from the developers to the administrators, but again, if the number of administrators is small, there is more responsibility upon that one person and so fewer people = smaller risk. That's just an example of course. Policies differ from organization to organization and how keen they are to achieve compliance such as Sarbanes-Oxley and the others whose name I forget.

[riteshjain1982]

usefull info,yes u r right,the most effective way is to keep developer away from production environment.

btw i really had very tought time with client IT guys when they were implementing sox,tough good from organiztion point of view but all these type of law suite wheather its bil198 or sox....they sux freedom

[mendhak]

I didn't understand your last sentence.

[Pradeep1210]

It seems ridiculous using the sa login for application deployment. The sa login is for DBA and should be used only for that purpose.

Atleast I never prefer the sa login for my applications, though it would give full access to my application.
This has two advantages:
1. The DBA and application developer roles are clearly segregated.
2. If someone somehow fiddles around with my application or otherwise my application does something wrong due to bugs etc. I'm always on the safer side and can't be blamed. Moreover the application itself will be limited not to fiddle around with the wrong tables. If I were using the sa account and someone hacks into it, I'm doomed

Pradeep