Securing DB Password in ASP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Securing DB Password in ASP

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-08-03 : 21:59:19

Although global.asa does provide some level of security, nevertheless, the uid/pwd are lying there in plaintext format.

"driver=SQL Server; server=sql.myserver.com;uid=myuid;pwd=mypwd;database=mydb"

Somewhere I saw an asp method of keeping the DB password NOT in plain text format. Of course I can't find it now so here I am.

Anyone know of techniques for either or both ASP and ASP.NET ?

SamC

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-08-03 : 22:39:03

Hi Sam,
This isnt the answer to your question but if you want to consider another method this is my solution. I create a login with no permissions what so ever. All access to the DB is done through stored procedures. I give permissions to execute the procedures and nothing else to the login. I use this login for all connection strings. This way I dont need to worry much if the security of the webserver is compromised as my data is subject to the same manipulation as in any other scenario.

Justin

bjornh
Yak Posting Veteran

87 Posts

Posted - 2002-08-05 : 06:24:16

isn't it so that maybe it is plain text, but an .asp page is always compiled except when you have the login to your site, so nobody can read the text..

CMartin
Starting Member

13 Posts

Posted - 2002-08-05 : 23:39:18

Justin,

If you had to use sp_executesql in one of your store procedures, would you give permissions to that login to the tables used in the dynamic sql statement, or is there other solution?

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-08-06 : 00:25:56

quote:

If you had to use sp_executesql in one of your store procedures, would you give permissions to that login to the tables used in the dynamic sql statement, or is there other solution?

No, I would create the stored procedure under the context of the dbo. I would then use "grant execute on MyProcedure to ASPLogin" to allow the ASP assigned login to execute the stored procedure. So even if they got my ASP assigned login they wouldnt be able to issue straight SQL statements to the table or create their own procedures to modify the data.

hth,
Justin

CMartin
Starting Member

13 Posts

Posted - 2002-08-07 : 03:31:45

Justin,

Given the following store procedure


CREATE PROCEDURE [dbo].[my_proc1]
@IntVariable INT

AS
SET NOCOUNT ON

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once. */
SET @SQLString =
     N'SELECT * FROM employee WHERE job_lvl = @level'

/* Specify the parameter format once. */
SET @ParmDefinition = N'@level tinyint'

EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @level = @IntVariable

and by following your previous explanation, how would the ASPLogin (that has only execute
permissions on SP) run the above dynamic SQL?
I could only run it successfully by given also to ASPLogin SELECT permissions to employee table.

Carlos

KHeon
Posting Yak Master

135 Posts

Posted - 2002-08-07 : 07:12:07

Where I work we are developing new and interesting ways to hide login credentials to whatever system(s) we might have to integrate with (SQL Server included).

One of the simpler ways you could protect your login information would be to use the WScript Encoder (or something like that) on Microsofts website. It allows you to encrypt an .asp file that IIS can decrypt during execution. Not sure on the performance hit, if any (I can't imagine there isn't), but there's always a trade-off (secure and slightly slower, or fast and unsecure). What you could do is create an include file with connection information in it, but the include file would be encrypted. Keep in mind though that this encryption isn't 100%, but it's a starting point.

Another option you could persue is to store connection information in the registry, even encrypt it there and then write a COM object that you can call to retreive and decrypt the connection information.

Good luck.

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-07 : 08:22:40

Kyle brought up the (Microsoft) standard way of doing it: create a custom COM object to handle database connectivity. You don't even need to encrypt anything. The COM object would create and return and ADO connection to a data source, and you instantiate it like any other ASP object. Once it's compiled all of the connection information is fairly well hidden (someone would have to hack the DLL on the web server to get at it...NOT an easy task) You can hard-code the user and password or allow the user to enter them. The only downside is that you may need to re-register the component on the web server whenever you make changes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources