SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2000 DBA Toolkit Part 1


SQL 2000 DBA Toolkit Part 1

Author
Message
AIM48
AIM48
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 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?


AIM48
AIM48
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 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.


Mike C
Mike C
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3747 Visits: 1168

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.


AIM48
AIM48
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 13
ok - thanks a bunch for the explanations
AIM48
AIM48
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 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?


Mike C
Mike C
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3747 Visits: 1168

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.


Richard Busse
Richard Busse
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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


Scott-364712
Scott-364712
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 42

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)


Mike C
Mike C
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3747 Visits: 1168
Yes, the encryption is tied to the local machine via the CryptoAPI. This is by design.
Scott-364712
Scott-364712
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 42
Is there any way around this? I need a way to implement public/private key encryption.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search