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.
| Author |
Topic |
|
shaymoh
Starting Member
1 Post |
 Posted - 2005-09-06 : 10:25:32
|
| Hi,I wonder if anyone can help me.I am a software developer writing code, (VB6 front end, SQL 2000 back end). for one of our clients, (a bank). Security is a major consideration.I needed to import large amounts of data from a file, and post this data to customer accounts. Using the Front End was way too slow, so, I decided to use the "xp_cmdshell" extended SP. My data import SP does, among other things, the following:1. enable "xp_cmdshell" EXEC permission to a small group of users, using the "sp_set_sqlagent_properties" SP;2. setup a proxy account using the "xp_sqlagent_proxy_account" SP;3. import the data using the "xp_cmdshell" SP.4. disable "xp_cmdshell" EXEC permission for non dba's, (this automatically closes the proxy account).My code, which works OK, looks something like the following:CREATE PROCEDURE Test AS DECLARE @Domain VARCHAR(10), @NTUser VARCHAR(120), @Password VARCHAR(10) ..... .....SELECT @Domain = Value FROM TestTable (NoLock) WHERE ModuleName = 'DomainName'SELECT @NTUser = Value FROM TestTable (NoLock) WHERE ModuleName = 'UserName'SELECT @Password = Value FROM TestTable (NoLock) WHERE ModuleName = 'Password' --Give non dba users CmdEXEC permission on xp_cmdshell EXEC msdb..sp_set_sqlagent_properties @sysadmin_only = 0 --Setup the Proxy Account EXEC master..xp_sqlagent_proxy_account N'SET' , N'@Domain' , N'@NTUser' , N'@Password' --Run the xp_cmdshell code EXEC master..xp_cmdshell ..... --Remove CmdEXEC permission on xp_cmdshell for non dba users EXEC msdb..sp_set_sqlagent_properties @sysadmin_only = 1 ..... .....My question is this. Is there any way of encrypting just the Password value in the TestTable above, then decrypting it within the SP? Encrypting the entire Value column in TestTable is not an option since lots of other SPs use this table.Adding my own Extended SPs to the database is also not an option.I suppose I could write my own encryption into the calling SP, using Caesar Shift or ROT13 or something, but I was hoping for a more elegant/builtIn solution.Can anyone PLEASE, PLEASE, help me.Thanks in advance,sc. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-09-06 : 11:15:27
|
| The problem with this technique is that it opens a hole in standard security for a period of time (albeit possibly a very small one), does a job and then closes the hole. The preferred solution, must be to work within the confines of a locked down environment. What happens if the job failed before the 'security hole was closed'?Running a job (scheduled???) under a SQL Server account with the correct rights would be preferable. Investigate also BCP and/or DTS. These can, securely, pump large amounts of data into SQL very fast. |
 |
|
|
|
|
|
|
|
encryption/decryption in SQL