Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

SQL 2000 DBA Toolkit Part 1 Expand / Collapse
Author
Message
Posted Monday, June 5, 2006 2:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 16, 2008 12:16 PM
Points: 6, Visits: 13

I didn't - but isn't that why I am encrypting the data - so that if somebodu does get a login all hell see is gibirsh.

If the user does not get a login in the first place - then what's the problem , why even encrypt?

Post #285035
Posted Monday, June 5, 2006 3:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 16, 2008 12:16 PM
Points: 6, Visits: 13

Ok - sorry again but I am having trouble running the functions from a non master database - I keep on getting

Could not find stored procedure 'dbo.xp_encrypt_aes'. The statement has been terminated.

Do you have any advice thanks.

Post #285041
Posted Tuesday, June 6, 2006 7:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

Extended stored procedures live in the master database.  The script to install the functions should be installing them to the master database as well.  Did the install script you downloaded not install the functions to the master database?  If you are accessing the functions from another database, the format is master.dbo.function_name.

I guess I'm not understanding your other question.  Column level encryption prevents someone from being able to see the raw data in the tables.  It's part of an overall security system that includes physical security, communication security, etc.  If someone were to get an admin login, for instance, they will be able to do pretty much anything they want to do on your server.

Post #285219
Posted Tuesday, June 6, 2006 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 16, 2008 12:16 PM
Points: 6, Visits: 13
ok - thanks a bunch for the explanations
Post #285239
Posted Tuesday, June 6, 2006 8:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 16, 2008 12:16 PM
Points: 6, Visits: 13

Also regarding logging in - I am in a funny situation where I have users (i.e. other developers) logging in to the db with full rights (it would be very hard to lock down access to this one table  etc...) - and I don't want them to be able to see the data in a specific field.

I guess for my situation the best approach would be for the app layer (which they do not have access to) to send the data preencrypted to the database. is that correct?

 

Post #285263
Posted Tuesday, June 6, 2006 9:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

Take a look at the documentation   If you specify NULL as password2 for the Master Key, it ties data encrypted with it to the account under which it's encrypted.  So you can tie decryption of data to the same account that it was encrypted with by not supplying the second password.  That means that not only would a maluser need a login to decrypt your data, they need the same login that was used to encrypt the data.

This is more secure, and can help prevent other users from getting into your encrypted data.  The downside is that only the account that encrypted the data can decrypt the same data.

Post #285287
Posted Wednesday, August 9, 2006 5:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 19, 2014 12:52 PM
Points: 2, Visits: 56

Nice work!  You should get some kind of award for this.  It is very cool.

Can I see the code behind the following DLL's? I'm concerned I may need it for my internal documentation in case anyone asks how it works:

  • xp_decrypt_aes.dll
  • xp_encrypt_aes.dll

Also, do you know if it is possible to create extended stored procedure DLL's with Visual Basic .NET?  Or is C# required?

Thanks!

Rick

MCP,MCAD,MCSD

 

 

Post #300741
Posted Wednesday, September 6, 2006 7:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 9, 2007 8:58 AM
Points: 2, Visits: 2

After installing the extended procs, etc I ran into an issue passing encrypted data from one db server to another.  I wrote some simple SQL to show this.  If I run the below SQL on server A and then server B I get diferent values for the encrypted data.  So when I encrypt data on Server A and then attempt to decrypt on Server B, I get Null.

As you can see, I bypassed the key tables and am passing the master and local keys directly.  Is there something else that the encryption code is using that would make encryption values unique for each server?

 

Server A:

0x424E750E96151DB65F6C6750A4ABA91A
test

Server B:

0x19E7D34C42A2271F34BB69CFC2529173
test

 

DECLARE @encText VARBINARY(8000)
DECLARE @plainText VARBINARY(8000)
declare @tmp varbinary(100)

set @tmp = convert(varbinary,'test')

EXEC master.dbo.xp_encrypt_aes @tmp,
 @encText OUTPUT,
 'pw12345',
 0x2BFD1CC6C094293DD6FD053809B68E3517BA255101BE5AA55DE3EBC501E7BFD1DBB59FE722263CB4699830871627570F1E73DD5FAA23A5BEC642218C109A524F,
 0xF2909C4FE472D92D1221747CAFE59D8BA078CEDC8AECB3BC3D83BBE7F595C585115D9CCEAC263C4B5884B5958EB82CBC813114E8A3192172F18D8540B268E00AB2A70BA9573708BD2419A26E2AA78159,
 256
select @encText


EXEC master.dbo.xp_decrypt_aes
 @encText,
 @plaintext OUTPUT,
 'pw12345',
 0x2BFD1CC6C094293DD6FD053809B68E3517BA255101BE5AA55DE3EBC501E7BFD1DBB59FE722263CB4699830871627570F1E73DD5FAA23A5BEC642218C109A524F,
 0xF2909C4FE472D92D1221747CAFE59D8BA078CEDC8AECB3BC3D83BBE7F595C585115D9CCEAC263C4B5884B5958EB82CBC813114E8A3192172F18D8540B268E00AB2A70BA9573708BD2419A26E2AA78159,
 256
select convert(varchar,@plaintext)

Post #306514
Posted Wednesday, September 6, 2006 8:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
Yes, the encryption is tied to the local machine via the CryptoAPI.  This is by design.
Post #306543
Posted Thursday, September 7, 2006 6:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 9, 2007 8:58 AM
Points: 2, Visits: 2
Is there any way around this?  I need a way to implement public/private key encryption. 
Post #306864
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse