If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Security questions.

SQL2K SP4

The apps that connect to my SQL DB's (for internet use) use SQL
authentication (the app login so to speak). Anyways, a Java developer showed
me that he built into his app a way to retrieve that app login and password
from SQL Server. Obviously I wasn't very happy about this. So my questions:

1; Does anyone know if the same thing can be done using .NET code?
2; I've heard of a method using .Net Web Services (WS) for using WINNT
authentication even for internet apps. It would be that the app calls a WS,
that WS the passes in WIINT authentication to the DB and all is good. Has
anyone else out there used this type of security? Is there a link you can
provide?
3; If number 2 is implemented, does it then eliminate the possibility of
apps being able to retieve the sensitive information?

In order to get the login information from the SQL Server system tables, he would have to log into SQL Server. If he had done that, didn't he already know the login name and password?

As the Car Talk guys would say, this sounds bo-o-o-o-gus. At least on the surface.

Well, sort of. We have a strange environment here. Developers write the code, QA changes the password in the config file, then migrates it into Production. So technically no, the developer doesn't (shouldn't) know the Production password.

I have seen your option 2 used plenty of times before. It has the benefit of not relying on sql auth, which is discouraged because it usually means you have passwords lying around in plaintext in web.config files. If you use windows auth in sql server, you won't have to worry about passwords in web.config as all the connstrs will be "trusted_connection=yes" which reveals nothing. You'll just have to grant access in your db to the nt account that the web service is running as.

It certainly doesn't remove the possibility of apps being able to retrieve sensitive information however. they'll be able to get anything that the web service provides, so if the web service has perms to fetch protected data, the app can get it (albeit only through the web service).

In general, you want to avoid as much as possible exposing sql server on the internet. it's much safer to expose it through a web service or similar, and then only expose the webmethods that clients need. don't expose a ExecuteSql(string sql) WebMethod, for example! Strive for a minimal, but complete interface in everything you do :}

It should be easy to use an application pool, if the web application can run on windows 2003.

I think I see how the developer did it. He is reading the config file in the java application, and displaying it. Depending on how sensitive this information is, this may have to be reported to managers/HR.

I have seen your option 2 used plenty of times before. It has the benefit of not relying on sql auth, which is discouraged because it usually means you have passwords lying around in plaintext in web.config files. If you use windows auth in sql server, you won't have to worry about passwords in web.config as all the connstrs will be "trusted_connection=yes" which reveals nothing. You'll just have to grant access in your db to the nt account that the web service is running as.

Thats what I was looking for, thanks! The developers app queries the web.config and gets the password.

Originally Posted by jezemine

It certainly doesn't remove the possibility of apps being able to retrieve sensitive information however. they'll be able to get anything that the web service provides, so if the web service has perms to fetch protected data, the app can get it (albeit only through the web service).

I know that the web service will be able to get sensitive data and thats fine. My concern is more people getting this datafrom outside the app (Access, etc.), as well as the password becoming public knowledge.

It should be easy to use an application pool, if the web application can run on windows 2003.

I think I see how the developer did it. He is reading the config file in the java application, and displaying it. Depending on how sensitive this information is, this may have to be reported to managers/HR.

An app pool can be configured to log on with a Windows account, just like a service. You can then grant the application's required permissions to that Windows account. That windows account does not even have to be mentioned in the application's config files, I believe.

Still. I would report this developer at least to managers in your department. There is no telling what other back doors he has left himself.