Database Setup Scripts

• Jim Mace

  SSC Eights!

  Points: 820

  More actions

  March 11, 2011 at 9:41 am

  #234978

  I have an ASP.net application that is installed using an MSI. The user has three options during the installation: 1) Install the application, set up the database and IIS automatically, 2) Extract the application files without setting up IIS while setting up the database, 3) Extract the application and database files to their respective directories where the user can manually set up IIS and run the database scripts.

  For options 1 & 2 the user is asked for the server location and user credentials to create a login, database, database user, tables, views, stored procedures, and functions. The MSI then executes the SQL statements. The user never has access to these scripts which hides the login's password that the database and application uses.

  Now for option 3, the scripts that are run for options 1 & 2 are extracted to a selected directory. This option was included as a request from some of our clients who use some complex network topologies where the user running the installer will never have the proper permissions to execute the scripts so they are then sent to the DBA to run against the SQL Server. We feel that there is a security hole here as now two people have the application/database password.

  Is there anyway that we can obfuscate at least the password if not the entire SQL scripts, four option 3, without some complex encryption/decryption mechanism?

  Thank you in advance,

  Jim

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 11, 2011 at 9:45 am

  #1297746

  Jim you could use something like this example, whether you need a string or a full command;

  it's just converting to varbinary and pasting the string into the final script;

  --obfuscate a command to send to the client:

  Declare @cmds Nvarchar(MAX)

  Declare @obfoo varbinary(MAX)

  Set @cmds = '

  PRINT ''This binary string will execute "SELECT * FROM SYS.OBJECTS":''

  SELECT * FROM SYS.OBJECTS

  '

  Set @obfoo = CAST(@cmds as varbinary(MAX))

  Select @obfoo

  --the resulting line above is copied and pasted into the "final" script;

  --the line below is the obfuscated command.

  declare @_ as varbinary(max)

  set @_ =0x0D000A005000520049004E0054002000270054006800690073002000620069006E00610072007900200073007400720069006E0067002000770069006C006C002000650078006500630075007400650020002200530045004C0045004300540020002A002000460052004F004D0020005300590053002E004F0042004A00450043005400530022003A0027000D000A00530045004C0045004300540020002A002000460052004F004D0020005300590053002E004F0042004A0045004300540053000D000A00

  exec (@_)

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!