Endless subqueries

I have a table with two columns: OID and Cumulative (witch is the same type as OID)

Each OID can have one or more Cumulatives.

Example of data:

OID Cumulative

167 292

167 294

167 296

168 292

169 302

169 304

The cumulation of each OID don't stop at one cumulation, but can be endless (theoretical).

Example: 167->292->590

So the table would have on more row:

OID Cumulative

295 505

I would like to represent this strucuture in a tree view and I'm looking for a query that could give me a table with this structure:

OID Cumul1 Cumul2 Cuml3 Cuml4 .... Cumuln

in the way I can read the row and have as many child nodes as I have values in the columns. The number of columns depends on the row with most cumulations.

How can I do the query?

Is there a better way as my table with n columns?

Thanks for suggestions

Your sample data is confusing..Can you fix it. Let us know the sample output from the input (sample data) you provided.

Encryption SQL 2005

I have a desire to encrypt an entire database rather than utilizing TSQL to encrypt individual columns. Outside the SQL Server authentication and access should function as normal.

Reason: avoid customization and change to a vendor applicaiton, and satisfying the group security ghouls by being able to state definatively that the data within the database is encrypted.

The database is small as it contains only financial statement data, so performance should not be an issue.

You may gain some insight from this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1784536&SiteID=1

Encryption in SSIS Package

Hello,

I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.

JatinShah wrote:

Hello,

I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.

I think Donald Farmer's book contains some information on how to do this. You'll be able to find it at all the usual places.

-Jamie

|||

You would need to use the Script Component, so you can use VB.Net to do the work.

Have you found out how to write the encryption functions in VB.Net, if not try this-

Walkthrough: Encrypting and Decrypting Strings in Visual Basic
(http://msdn2.microsoft.com/en-us/library/ms172831.aspx)

Then just wrap that into a Script Component.

|||

Hello Jamie,

Could you please get me the name of the book.

Thank You

Jatin Shah

|||

JatinShah wrote:

Hello Jamie,

Could you please get me the name of the book.

Thank You

Jatin Shah

http://amazon.com/s/ref=nb_ss_gw/102-7891523-4086513?url=search-alias%3Daps&field-keywords=donald+farmer

Encryption in SSIS Package

Hello,

I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.

JatinShah wrote:

Hello,

I want to import data from a excel source to SQL Server 2005 using SSIS. Among all the calls columns to be imported, there is 1 column which needs to be encrypted using asymmetric encryption and stored in destination table. Can anybody guide me how to program SSIS package using encryption function.

I think Donald Farmer's book contains some information on how to do this. You'll be able to find it at all the usual places.

-Jamie

|||

You would need to use the Script Component, so you can use VB.Net to do the work.

Have you found out how to write the encryption functions in VB.Net, if not try this-

Walkthrough: Encrypting and Decrypting Strings in Visual Basic
(http://msdn2.microsoft.com/en-us/library/ms172831.aspx)

Then just wrap that into a Script Component.

|||

Hello Jamie,

Could you please get me the name of the book.

Thank You

Jatin Shah

|||

JatinShah wrote:

Hello Jamie,

Could you please get me the name of the book.

Thank You

Jatin Shah

http://amazon.com/s/ref=nb_ss_gw/102-7891523-4086513?url=search-alias%3Daps&field-keywords=donald+farmer

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Back up and restore database on another server, decrypting the data results in NULLs being returned. Backed up and restore the master key, cerficate, etc but either it completes successfully or SQL states that the old and the new keys are the same and wont be changed. Master key opens (using password), cert opens (is viewable in sys.openkeys) but then closes automatically after trying to decrypt. Suggests there is something wrong with the certificate or the key, but I have no idea what it is.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

When you restore the database on the other server, you will need to add the service master key encryption to the database master key. You don't need to individually backup and restore anyhting else (don't worry about individually restoring the master key and the certificate). See this post for additional information on the master keys: http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx.

If you opened the master key using a password, things should also work with no change.

But the certificate cannot be "opened" and it never shows in sys.openkeys. What you need to open is the symmetric key and this should be done following the same steps that you did in the original database.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Tomorrow I will run through the procedure again using new databases, and post all the scripts I used for the encryption and restoration on the second server (it wont be pages full, merely one line per activity and someone else might get benefit from it as well)

|||

This ALTER MASTER KEY statement is the only thing you'll need to issue to complete the restoration of the database. Everything else should already be recovered with the database itself. Let us know if you hit any issues.

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Repeated the restore again with another database, same steps, same result, NULLs when decrypting. The key is open and in sys.openkeys (cant open the key without opening the master key first), try to use the decryptbykey and the are no values returned (key remains open). Restarted SQL, key opens fine but still NULLs get returned but now the key gets closed automatically after the first decryptbykey attempt.

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

The behavior that you describe is very strange indeed. Could you post the T-SQL that you used to open the key and to decrypt? I will try to repro this behavior, but because of holidays, I may not be able to do this very soon.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Hi

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

We have tried the scenario that you describe, moving a database between a Windows 2000 and a Windows 2003 machine, and we were successful in decrypting data after restores. We were not able to reproduce the problem that you described.

You mentioned that data becomes available after a while. How much time did it took before you could decrypt? Could you post the entire script that you are executing for encrypting the data and then decrypting it after restoring the database?

I'd also suggest to try to encrypt and decrypt some value after the restore to verify if the behavior that you are noticing is also happening for data encrypted after the restore. If the encryption would fail as well, then that would indicate an issue with the key.

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

What usually happens with the data is that is becomes available the next morning when I am back at the office, after playing with it in the afternoon and not getting any results. I have tried things like restart SQL and reboot the server to see if that would speed up the recovery, but it didnt help anything. Nothing happens in the server during that time, I just get in, open the symm key and it decrypts.

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

I retried this after the upgrade from Dev to Enterprise, but I still had the problem occuring. I am able to open the key (appears in sys.openkeys) but inserting any values into the 2000 database just insert NULLs into the encrypted column. So the key doesnt seem happy to do encryption or decryption.

I then backed up the database on the 2000 server, and restored it again on the 2003 server (where it came from originally) under a new name. Again I opened the master key and did the service master key change, opened the symmetric key and was able to view the information. So yeah, it seems everything is fine on the database side, just something on the 2000 set up is causing some issues.

This is a bit of a mystery, I have left the machines and will check it tomorrow morning to see if the 2000 machine magically starts to decrypt again. There arent any jobs or policies on them and they are purely standalone.

Is there any trace or debugging that I can enable that might give some more info?

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Back up and restore database on another server, decrypting the data results in NULLs being returned. Backed up and restore the master key, cerficate, etc but either it completes successfully or SQL states that the old and the new keys are the same and wont be changed. Master key opens (using password), cert opens (is viewable in sys.openkeys) but then closes automatically after trying to decrypt. Suggests there is something wrong with the certificate or the key, but I have no idea what it is.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

When you restore the database on the other server, you will need to add the service master key encryption to the database master key. You don't need to individually backup and restore anyhting else (don't worry about individually restoring the master key and the certificate). See this post for additional information on the master keys: http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx.

If you opened the master key using a password, things should also work with no change.

But the certificate cannot be "opened" and it never shows in sys.openkeys. What you need to open is the symmetric key and this should be done following the same steps that you did in the original database.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Tomorrow I will run through the procedure again using new databases, and post all the scripts I used for the encryption and restoration on the second server (it wont be pages full, merely one line per activity and someone else might get benefit from it as well)

|||

This ALTER MASTER KEY statement is the only thing you'll need to issue to complete the restoration of the database. Everything else should already be recovered with the database itself. Let us know if you hit any issues.

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Repeated the restore again with another database, same steps, same result, NULLs when decrypting. The key is open and in sys.openkeys (cant open the key without opening the master key first), try to use the decryptbykey and the are no values returned (key remains open). Restarted SQL, key opens fine but still NULLs get returned but now the key gets closed automatically after the first decryptbykey attempt.

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

The behavior that you describe is very strange indeed. Could you post the T-SQL that you used to open the key and to decrypt? I will try to repro this behavior, but because of holidays, I may not be able to do this very soon.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Hi

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

We have tried the scenario that you describe, moving a database between a Windows 2000 and a Windows 2003 machine, and we were successful in decrypting data after restores. We were not able to reproduce the problem that you described.

You mentioned that data becomes available after a while. How much time did it took before you could decrypt? Could you post the entire script that you are executing for encrypting the data and then decrypting it after restoring the database?

I'd also suggest to try to encrypt and decrypt some value after the restore to verify if the behavior that you are noticing is also happening for data encrypted after the restore. If the encryption would fail as well, then that would indicate an issue with the key.

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

What usually happens with the data is that is becomes available the next morning when I am back at the office, after playing with it in the afternoon and not getting any results. I have tried things like restart SQL and reboot the server to see if that would speed up the recovery, but it didnt help anything. Nothing happens in the server during that time, I just get in, open the symm key and it decrypts.

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

I retried this after the upgrade from Dev to Enterprise, but I still had the problem occuring. I am able to open the key (appears in sys.openkeys) but inserting any values into the 2000 database just insert NULLs into the encrypted column. So the key doesnt seem happy to do encryption or decryption.

I then backed up the database on the 2000 server, and restored it again on the 2003 server (where it came from originally) under a new name. Again I opened the master key and did the service master key change, opened the symmetric key and was able to view the information. So yeah, it seems everything is fine on the database side, just something on the 2000 set up is causing some issues.

This is a bit of a mystery, I have left the machines and will check it tomorrow morning to see if the 2000 machine magically starts to decrypt again. There arent any jobs or policies on them and they are purely standalone.

Is there any trace or debugging that I can enable that might give some more info?

Encryption - Protect from attacks by programmers?

I can encrypt columns in sql 2005 but where do I store the key to decrypt the columns?

I can store the key in the database (or server on which the database resides) but I think that offers little security. I could store the key on another server that the sql server accesses only upon startup (though I don't know exactly how to do that). Or I could store the key on a removable drive that is read (and only needed) when the sql server starts up.

What are your ideas on this matter?

TIA,

barkingdog

Have a look at the encryption hierarchy in SQL Server: http://msdn2.microsoft.com/en-US/library/ms189586.aspx. Also check the other resources mentioned here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=286374&SiteID=1.

If you store the key in the database, it is stored encrypted. I am not sure why would you think that this scheme offers "little security", can you elaborate on that statement? Before discussing a protection scheme, it would be helpful to state what you are trying to protect against - against what attacks do you want to protect the key?

Thanks
Laurentiu

|||

Laurentiu,

From what you said about storing the key in the database I obviously have a misconception here. But how then, is one supposed to access the encrypted key to de-crypt the data for later display in the UI? Is there a "proxy" stand-in for the real key once it is encrypted?

My concern is to prevent outsiders from, if they somehow gained access to the database (say a stolen or lost backup tape), from being able to decode sensitive fields such as Social Security number. At the same time, when a SSN is entered via the UI the application needs the key to drive the encryption of sensitive fields.

Barkingdog

|||

Basically, there are two ways to encrypt keys (hence two ways to decrypt them): one is to eventually use a password, so the password needs to be specified when the key needs to be used; the second protection is based on DPAPI, so no password needs to be specified. DPAPI basically uses the credentials of the machine and of the service account to protect the key, so to break it, one would have to know those credentials.

For details on DPAPI, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/seccrypto/security/cryptprotectdata.asp. How DPAPI ties in to the key protection scheme is shown in the key hierarchy diagram from the first link in my previous message - you have a chain of encryptions rooted at the DPAPI encryption of the service master key.

For additional information and examples, see the blogs I referred to you earlier. The example from http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx has the symmetric keys protected so that no password is required for their use (access control for keys is done through permissions). The post from http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx goes over additional considerations related to the use of encryption keys. For a discussion of the protection conferred by encryption, see http://blogs.msdn.com/lcris/archive/2005/12/20/506187.aspx.

For the following explanation, I will assume that you have examined the key hierarchy diagram and that you understand the encryption chain. I will also use SMK and DbMK as shortcuts for the service master key and, respectively, database master key.

In the case of a stolen backup tape that contained a database with encrypted data, the thief cannot decrypt the data without knowledge of the password that protects the DbMK. So the data is secure because removing a database from the server will cut the database from the encryption chain that allows its data to be decrypted.

If you're worried about a stolen laptop scenario, this threat is a little different from the previous one because the thief might be able to figure out how to gain full control of the laptop so that he can connect to the server as usual and decrypt the data. For this scenario you would want to protect the encryption keys by password, and then have the password specified when you're accessing the data (think of it as an additional login operation that grants access to the encrypted data). This would protect against a lost laptop scenario because even with full access to the laptop, knowledge of the password is still required.

Thanks
Laurentiu

|||

Hi,

I have a different type of problem. Lets say I have created a Symetric key (without using a password) and i authorize that key to a user called ASP_NET_My_Appln.

This user is used by my UI(ASP.NET) for querying the DB.

All the programmers who are coding the project WILL know the key name that we are using for encrypting / decrypting the data.

Therefore any programmer who can log into the production server or for that matter the local environment (where we place production server dumps to get the latest data) can decrypt the data by passing a simple SQL like this:

select EncryptXXX(Key,Column) from table

To protect the same I had to resort to use a password phrase. But however there is a problem there too. All the SQLs that we use are stored in SPs. Therefore a sample SP would be:

Sp_GetData @.pwd varchar(10)
AS
OPEN SYMMETRIC...... Password=@.pwd
select EncryptXXX(Key,Column) from table
Close Symmetric...

Anybody who is running the profiler can now read the password as the profiler does not block the same.

What is the best way to overcome this?

Therefore categorising the problems:
1. As far as I see the decyprtion seems to be a very simple select statement. Therefore anyone who has access to the server and knows the correct Key name and table name etc can do the same (Which a programmer WILL know).
2. Profiler is capable of blocking the actual select statements that use encryption commands but NOT the SP that takes the password. How can I overcome that? Should I change my design? Once again the password cannot be hardcoded into an SP as any developer can open it and look into it.

Kindly correct me if I have misqouted anything.

|||

1. To decrypt, you need to have previously opened the key. The access checks on the key and the knowledge of the passwords used to access the key come into place at this time, and it is these checks that restrict the access and use of an encryption key.

2. You're right about not wanting to hardcode the password in a SP. You should treat the key password as a login password and issue a direct OPEN SYMMETRIC KEY statement whenever you want to use the key - the password passed to OPEN will not be traced.

However, I am not sure I understand your scenario very well. Why are your programmers manipulating sensitive data while developing the application? What kind of access to the database and to the sensitive data do they need?

Thanks
Laurentiu

|||>>However, I am not sure I understand your scenario very well. Why are your programmers manipulating sensitive data while developing the application? What kind of access to the database and to the sensitive data do they need?

Its like an internal application that deals with the data for the entire organisation (really sensitive data of employees).

So the programmers themselves MIGHT be hackers...

Since I have to give access to my UI user, anybody who gets hold on the connection string can open a connection to the DB and remove the data by a very simple select stmt. So to protect this I wanted to have passwords for the key. But now i am stuck as to how to protect the password from hackers...|||

As long as the password is not hardcoded in the application, for the programmers to see, they should not be able to get it from just examining the code. What are your concerns if the user is specifying the password to the application?

Thanks
Laurentiu

|||Hi,

When you mean "As long as the password is not hardcoded in the application, for the programmers to see, they should not be able to get it from just examining the code. What are your concerns if the user is specifying the password to the application?"

Are you talking about the UI? If yes then the problem arises when I have to pass it to the DB (to an SP in the DB).

Where and how exactly do you want me to store the password that protects the key?|||

I am suggesting to have the user specify the password. I am not suggesting for the password to be stored somewhere where it can be programmatically retrieved, given that you are trying to prevent the developers of your application from accessing it. Also, I am not suggesting for the password to be passed around as an argument to stored procedures (which would make it visible in a trace) - it should just be passed to the OPEN SYMMETRIC KEY statement.

Thanks
Laurentiu

|||>>it should just be passed to the OPEN SYMMETRIC KEY statement

Exactly, but my open symmetric statement is inside an SP. There can be more than 100 SPs that have to access this password. In this case there are 2 options for me:

1. Hardcode the password in each SP.
2. Pass it as a parameter to the SP.

I choose the second one therefore the problem.|||

Why do you open the key inside the SP? Why can't you open it as part of the logon process for your application and keep it open for as long as you work with the encrypted data. Once you open a key, it is only available within the current session, so you don't have to worry about other users getting to it. Also, you don't need to open it and close it for each access to encrypted data. You can open it once, use it for many encryptions and decryptions (which can happen in stored procedures that you call - they will have access to the opened key), and then close it when you are done (or you can just disconnect your session and it will be destroyed).

Thanks
Laurentiu

|||Hi,

Before I implement your idea i would like to know more about the defenition of a current session.

I am using EntLib, therefore each call to an SP opens / closes a connection from the pool. How is the session defined in this case?

One more thing, to hide the data from the profiler i will have to pass the OPEN stmt as a direct SQL rather than using an SP right?|||

Hi,

You can use CLR function to open symetric key , The script will be in assembly

and the password is hidden at all.

Thanks,

Tarek Ghazali

SQL Server MVP

web site : www.sqlmvp.com

|||

Hi,

I am totally new to this. Could you possibly giude me to some tutorials on the same?

Encryption

We need to be able to encrypt very sensitive columns of data in a
quasi-warehouse type environment and, from what I've read, SS 2005 should
work quite well for this. But I need examples of how to implement this. I
know very little about encryption and so I need some easy-to-read examples of
how to encrypt/decrypt using stored procs, scripts and code in 2005. Where
can I find examples like this? Any sites, books, articles, links would be
much appreciated.CLM wrote:
> We need to be able to encrypt very sensitive columns of data in a
> quasi-warehouse type environment and, from what I've read, SS 2005 should
> work quite well for this. But I need examples of how to implement this. I
> know very little about encryption and so I need some easy-to-read examples of
> how to encrypt/decrypt using stored procs, scripts and code in 2005. Where
> can I find examples like this? Any sites, books, articles, links would be
> much appreciated.
There are some examples in Books Online...|||See Laurentiu's blog:
http://blogs.msdn.com/lcris/archive/category/10357.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e3lV0PUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> CLM wrote:
>> We need to be able to encrypt very sensitive columns of data in a
>> quasi-warehouse type environment and, from what I've read, SS 2005 should
>> work quite well for this. But I need examples of how to implement this.
>> I know very little about encryption and so I need some easy-to-read
>> examples of how to encrypt/decrypt using stored procs, scripts and code
>> in 2005. Where can I find examples like this? Any sites, books,
>> articles, links would be much appreciated.
> There are some examples in Books Online...

Encryption

CLM wrote:
> We need to be able to encrypt very sensitive columns of data in a
> quasi-warehouse type environment and, from what I've read, SS 2005 should
> work quite well for this. But I need examples of how to implement this.
I
> know very little about encryption and so I need some easy-to-read examples
of
> how to encrypt/decrypt using stored procs, scripts and code in 2005. Wher
e
> can I find examples like this? Any sites, books, articles, links would be
> much appreciated.
There are some examples in Books Online...See Laurentiu's blog:
http://blogs.msdn.com/lcris/archive/category/10357.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e3lV0PUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> CLM wrote:
> There are some examples in Books Online...|||We need to be able to encrypt very sensitive columns of data in a
quasi-warehouse type environment and, from what I've read, SS 2005 should
work quite well for this. But I need examples of how to implement this. I
know very little about encryption and so I need some easy-to-read examples o
f
how to encrypt/decrypt using stored procs, scripts and code in 2005. Where
can I find examples like this? Any sites, books, articles, links would be
much appreciated.|||CLM wrote:
> We need to be able to encrypt very sensitive columns of data in a
> quasi-warehouse type environment and, from what I've read, SS 2005 should
> work quite well for this. But I need examples of how to implement this.
I
> know very little about encryption and so I need some easy-to-read examples
of
> how to encrypt/decrypt using stored procs, scripts and code in 2005. Wher
e
> can I find examples like this? Any sites, books, articles, links would be
> much appreciated.
There are some examples in Books Online...|||See Laurentiu's blog:
http://blogs.msdn.com/lcris/archive/category/10357.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e3lV0PUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> CLM wrote:
> There are some examples in Books Online...

Encrypting SQL Server table columns

I have never used any type of encryption for SqL Server 2000, but I have recently been asked to research what would be involved to encrypt data like social security numbers.

Since I have no idea where to begin, I was wondering if someone could point in the right direction? Do I need to buy a third party tool to perform this, are security certificates involved? Overall, I need information on everything that I need to encrypt data using SQL Server 2000, and How to perform the tasks!!!

Thank You!

There is no built in data level encryption in SQL Server 2000 (there is in 2005) so you either need a 3rd party or to handle it in the App layer. The folks in the security group will likely have some pointers on 3rd party stuff.|||

As Euan already mentioned, I always liked the way doing encryption on the application / business layer in my application. You will have full control over your encryption and can easily hook some extra modules within if you want to. Otherwise, if you don′t have budget to built your own encryption layer or extend your current one, you can use thrid party tools. Those are normally presented by extended procedures which can be called for de-/encryption. Searching at Google for SQL Server +encryption will get you many hits for vendors of encryption solutions (Also use the adWords links on the right site)

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

Encrypted DB -- Restore Question

Hi,

I have a DB in which I encrypt a few columns in a table. I am using a Symmetric key to encrypt and decrypt the data. When I take a back up of this DB and restore on another server ... my decryption doesn't work. I have dropped the master key and recreated it with same password and that didn't help either.

What are the rules to follow when we restore a db on a different server that has encrypted data ? Thanks.

You will need to open the master key in the new database once ,so that a copy of the same is saved in the master database. After that the decryption should work. Let me know if this helped?|||How do I open Master Key? Thanks!|||

Hi,

You shouldn't have to do anything special to restore a db with encrypted values. The one thing you may need to do is to alter the master key of the database and re-encrypt with the service master key. You can do this with:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'your_password_here';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

(for more info, take a look http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx).

EDIT: whoops, wrong link, sorry, this should now have the correct link.

|||

I tried and that didn't help ...

I used OPEN MASTER KEY DECRYPTION BY PASSWORD = 'my pwd'

|||

OPEN MASTER KEY DECRYPTION BY PASSWORD = ['password']

|||What's the specific error you are seeing? Are you getting "NULL" back as the decrypted value or are you receiving a server error?|||Yes ... I get "NULL" and when I look at the data in the column, it does have encrypted data. Thanks for your help!!!|||Does the login that you are using having appropriate rights... I mean on the symmetric keys, certificates.|||Yes .. I have logged in as SysAdmin ...|||

One other tying you can try, before you decrypt, run "SELECT * FROM sys.openkeys" to verify that the symmetric key is ready for decryption.

|||Are the operating system on both the machines the same, because certain encryption types like AES are supported on certain type of OS.|||I looked at the owner info again ... and the original DB was created by "sa" account itself ... where as the attached DB is owned by another user that is of SysAdmin group. Does that matter for decryption? I really appreciate your help!!|||

The owner of the db shouldn't matter for decryption (especially since you confirmed you can select from the table without problems) as long as the user has not been denied view rights for the symmetric key. You also need rights on whatever is encrypting the symmetric key.

If the symmetric key shows up in sys.openkeys though, you should be fine as it means the key has been opened and ready for decryption.

Can you share the SQL you are using to decrypt the data?

|||Just a suggestion, try restore and backup instead of attachdb.

Encrypted Database transfer problem

Hi,

I have encrypted some columns of a table in a database. Following is the method which i applied for encryption.

I created a master key with a password and it is also encrypted by service master key. Now i created a certificate without password, so it is only encrypted by master key of the database. Now i created a symmetric key encrypted by the above certificate. The data is encrypted by this symmetric key.

To decrypt data i use DecryptByKeyAutoCert.

On my server this encryption & decryption is working perfectly.

But when i take this database to another server, it is not working.

What is the solution for this, should i drop service master key to encrypt master key or is there any soln.

Thank you.

Pls give me soln. i am worried abt it.

Gaurav

See last paragraph in: http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx.

After you restore your database, you need to readd the service master key encryption of the database master key.

Thanks
Laurentiu

|||

I have attached a database created with master key, certificate and symmetric key.

Now i have attached this database to another server but no master key, certificate or symmetric key is present there in the new server database.

Do i need to backup master key and certificate and then restore them to the new server database? Then what abt symmetric key. there is no option for symmetric key to be backedup.

what is the soln for symmetric key? and taking backup of master key and certificate, then restoring it is the only soln?

Thanks

Gaurav

|||

You don't need to do anything else when moving a database from one server to another, other than what I mentioned before: restore the SMK encryption of the DbMK, if such an encryption existed on the source server.

How did you verify whether the keys are present in the database after you reattached it? Did you look in the catalogs (sys.symmetric_keys, sys.certificates)?

Thanks
Laurentiu

Encrypted columns and searches (partial and full)

Hi,

For those implementing encrypted columns, what is the recommended approach when allowing users to also do partial searches on encrypted data? (ie email or creditcard info where the tables contain millions of rows). I understand one cannot have the encryption without performance impact, but the searches can be 10 to 20 times as long as when the info is stored in normal char(20) columns. Just looking as a way to try and lessen the impact.

Thanks

Prompted by your question, I just posted a new entry to my blog about how to enable searches on encrypted data. The entry is at:

http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx.

Please take a look at it and let us know if you have additional questions.

Thanks
Laurentiu

encrypt database --

Hi,
Sorry for the message in spanish I confused the group.
Is there a way to encrypt a database (tables, columns, etc.) ? I want that
nobody can see the data model of my application
Probably it's impossible to deny access to the database, but at least if
someone is trying to copy my design he/she will see the name of objects,
columns, and others encrypted. (For example instead of see the table
ARTICLE, see symbols ="!!$%&/- )
I'll appreciate your comments.
Edmundo J. DavilaYou can assign certificates and encryption keys under the security
folder of a database in SQL Server Management Studio, however, these
only encrypt the data stream that is sent from a SQL Server instance
to any SQL Server Agents.
If you install SQL Server to run from inside of Microsoft Virtual
Server, then the entire database file will be stacker compressed which
would be unreadable to human eyes. But, with any kind of encryption,
SQL Server will have to spend time decrypting fields as it walks any
kind of search and this will cause a significant performance hit.

encrypt credit card details within SQL 2000/2005

Hi, I am hoping someone could shed some light on encrypting columns within
database tables.
What I need to do is encrypt the credit card field of a sql table. What is
the best way of going about this? This doesnt seem to be well documented.
Any help most appreciated.
Cheers, PeterSQL Server 2000 does not provide encryption functionality out of the
box, you will have to either do this on the client and sending the
already encrypted data to the server or send the data to the server
(you will have to be aware of man-in-the-middle attacks and consider
protocol encryption for securing this) and encrypt it either using
your own encryption algorythm or any other third party procedure
(often xp_s) to do this. SQl Server 2005 intriduced a new encryption
functionalty, based on either certificates or passphrases, not to
extened this explanation further you can read a lot about that in the
BOL or on the internet.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||For SQL 2005 look up the built-in T-SQL Encryption functionality in BOL.
For SQL 2000, either do it client-side as Jens suggested, or get some
utility XP's like this:
http://www.sqlservercentral.com/col...oolkitpart1.asp
"peter walker" <p.walker@.nospam.com> wrote in message
news:eb3yxLeRHHA.4632@.TK2MSFTNGP04.phx.gbl...
> Hi, I am hoping someone could shed some light on encrypting columns within
> database tables.
> What I need to do is encrypt the credit card field of a sql table. What is
> the best way of going about this? This doesnt seem to be well documented.
> Any help most appreciated.
> Cheers, Peter
>