February 24, 2003 at 9:35 am
I would like to eliminate all hardcoded login ids and passwords and would like to know what other companies are doing to address this issue. I know a job scheduling software package is the ideal solution, but until dollars become available I need a short-term solution. We've tossed around the idea of using system variables.
Any recommendations?
February 24, 2003 at 9:40 am
We are running all of our jobs using the SQL Server job scheduler. All these jobs are connecting to SQL Server using a trusted connection.
Another possible option we have thought about although have not used, is to store the SQL Server login passwords in an encrypted table, and the dynamically generate the username, and password. Although this method is truely not totally secure, it is better than having a hardcoded password in a script someplace.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
February 24, 2003 at 10:06 am
Greg,
Using SQL Server scheduler is one option I have considered for a more long-term solution, but since we have over 100 scheduled jobs, it will take some time to convert all of them.
I also considered using a single table to store login ids and passwords. How did you plan on dynamically generating the username and password? I thought about creating a control job that would coordinate the launching of all batch jobs. Each job would have to call the control job, which would then connect to the SQL table to retrieve the username and password. The control job would then execute the corresponding batch jobs. I have yet to detail how this would work so right now it is just theory.
Thanks, Dave
February 24, 2003 at 10:22 am
We use a similar technique to what Greg described. There are a couple of
considerations AFAIK :
1) If using single key encryption do not use a HARD CODED string in your
executable / dll for the key. These show up in HEX editors when inspecting the
binary. I use a simple technique for this :
Concatenating the string dynamically, IE(strKey = Chr$(38) & Chr$(44) & etc....)
I usually generate a GUID for these kind of keys, and then turn it into a
string concatenation operation.
Of course this does not prevent them from using a memory inspection tool, to
slurp your memory, or dissembling your binary.
2) Encrypt the user names as well, and not using SQL's encryption, which is weak.
Knowing the user name is a GREAT start for a hacker.
3) Use the crypto API or the MSFT com wrapper for it @
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsecure/html/intcapicom.asp
or whatever 3rd party tool you wish. I wrote a wrapper for the Crypto API before
I knew of CAPICOM.
4) Use one encryption / decryption method for the user name, and one for
the passwords. If you want as well for the private key you can include
database / machine information in the encryption / decryption routine so that if
they are moved from machine to machine, the encryption / decryption would have
to be redone.
5) Etc, Etc.... IMHO, One cannot be too paranoid when it comes to security.
Tim C.
//Will write code for food
Tim C //Will code for food
February 24, 2003 at 10:25 am
I also have not detailed this out at all, but generally this is what I am thinking.
There would be a process that would read the encripted login and password, then the process would dynamically build the batch script. Once the script was built it would run the script, and then delete the script at the end. Of course the process that would run to read the login and passwords itself would need a login or password, or be an trusted user.
I've only thought about this in theory, I have yet to build anything. Although I will be soon, so if you come up with a good approach I will be all ears.....
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply