encryption with certificate

I am trying to create a encrypted row in my database
Everything here worked except that when i run the final query to decrypt the data
It just comes up with null for each row. Even if i do a query to show me the rows that are not null
It's like it is saying yeah there is data here but I am only going to show you null instead of what I am supposed to decrypt.
Here is what I tried from start to finish

Create Certificate TestCertEncryptionBy Password ='Password'With Subject ='SQLCert',Expiry_Date ='12/01/2050';declare @.Testnvarchar(50)set @.Test='123456789'insert into testenc (testencry)Values (encryptbyCert(Cert_ID('TestCert'),@.Test ))selectconvert (Nvarchar(50),DecryptByCert(Cert_ID('TestCert'),testencry,N'Password'))As Testfrom testenc

I am using sql 2005 by the way|||Nevermind Just realized I need to use VarBinary instead of NvarChar to store the data!

Encryption Question - Urgent!

Hi,

I encrypt a column in a table. I am able to decrypt/encrypt the same successfully. However, when I copy the encrypted data to a new database and try to decrypt using the same certificate, it doesn't work. I have created the same "Master Key" and certificates on the new DB .... So, is it possible to decrypt the encrypted data that is transferred from one DB to another? If not, are there any alternatives ?

I have tried opening the master key on the new DB using the following:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pwd';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

Thanks.!

How do you encrypt/decrypt and what error messages are you seeing, if any? Please describe the steps that you followed in more detail.

Thanks
Laurentiu

|||

Here are the details:

In DB1

1. I created a master key (CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pwd')

2. Cerated a certificate (CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'xxx')

3. Created Symmetric key (CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME).

Then in DB1, I encrypted a column in a table using ENCRYPTBYKEY. I am also able to decrypt using DECRYPTBYKEY.

Then I inserted the encrypted column into another db with same table structure.

INSERT INTO DB2.dbo.table1(enc_column)

SELECT x.enc_column

FROM DB1.dbo.table1 x

now in DB2, I am not able to decrypt the "enc_column". when I use the same syntax I get a "null".

|||

Thanks for the details. The reason why you cannot decrypt in the new table is because you have no decryption key in the new database.

If you plan to move encrypted data from one database to another, you should create the symmetric key used to encrypt the data in both databases. The way you can do that is by using the same IDENTITY_VALUE and KEY_SOURCE parameters for the CREATE SYMMETRIC KEY statement. Of course, you should use the same key algorithm, as well.

Thanks
Laurentiu

|||

Also, see this recent post for a demo of how to do this:

http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx

Thanks
Laurentiu

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?

Encrypting a Single Row/Column

Hi,
Is there any way to encrypt and decrypt a Single Row/Column in SQL
server 2000 and SQL server 2005.
I do not find any info w.r.t it in the documentation.
Thx in advans,
Karthik BalaguruOn Jul 25, 12:04 pm, karthikbalaguru <karthikbalagur...@.gmail.com>
wrote:
> Hi,
> Is there any way to encrypt and decrypt a Single Row/Column in SQL
> server 2000 and SQL server 2005.
> I do not find any info w.r.t it in the documentation.
> Thx in advans,
> Karthik Balaguru
Hi,
I found the below link having some info w.r.t Encrypt. But it is for
SQL Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
http://groups.google.co.in/group/microsoft.public.sqlserver.programming/browse_thread/thread/fd4a204efd4a69a8/?hl=en#
Thx in advans,
Karthik Balaguru
-->>
You can use the encrypt function even if you don't know what's the
encryption algorithm.
It will encrypt your data using the same algorithm that is used to
encrypt
stored procedures, triggers and views.
create table users(
usernamed varchar(25),
pass varbinary(16)) -- 8 characters password
insert into users values('user1', encrypt('123'))
insert into users values('user2', encrypt('456'))
insert into users values('user3', encrypt('789'))
select * from users
usernamed pass
-- --
user1 0x3E16CDED63C6
user2 0x3B3AB5941B3C
user3 0x383C6F64E6D0
(3 row(s) affected)
select * from users where pass = encrypt('123')
usernamed pass
-- --
user1 0x3E16CDED63C6
(1 row(s) affected)
Remember that encrypt is an undocumented and unsupported function. As
such
data encrypted with the function might become corrupted in a migration
to a
later version of SQL Server.
The encrypt function changed from SQL Server 6.x to
SQL Server 7.0, and individuals that used the encrypt function to
encrypt
data in SQL Server 6.x had such problems.
-->>
I find that the above has some info w.r.t Encrypt. But it is for SQL
Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
Thx in advans,
Karthik Balaguru|||Hi,
Please check:
How to: Encrypt a Column of Data from SQL 2005 BOL
Also:
See the following links for information on how to search encrypted
data:
http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx
http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx
HTH,
Dinesh|||On Jul 25, 12:59 pm, dinu_b...@.hotmail.com wrote:
> Hi,
> Please check:
> How to: Encrypt a Column of Data from SQL 2005 BOL
> Also:
> See the following links for information on how to search encrypted
> data:
> http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspxhttp://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx
> HTH,
> Dinesh
Thx Dinesh, Looks interesting.
Karthik Balaguru

Encrypting a Single Row/Column

Hi,
Is there any way to encrypt and decrypt a Single Row/Column in SQL
server 2000 and SQL server 2005.
I do not find any info w.r.t it in the documentation.
Thx in advans,
Karthik BalaguruOn Jul 25, 12:04 pm, karthikbalaguru <karthikbalagur...@.gmail.com>
wrote:
> Hi,
> Is there any way to encrypt and decrypt a Single Row/Column in SQL
> server 2000 and SQL server 2005.
> I do not find any info w.r.t it in the documentation.
> Thx in advans,
> Karthik Balaguru
Hi,
I found the below link having some info w.r.t Encrypt. But it is for
SQL Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
http://groups.google.co.in/group/mi...d4a69a8/?hl=en#
Thx in advans,
Karthik Balaguru
-->>>>>
You can use the encrypt function even if you don't know what's the
encryption algorithm.
It will encrypt your data using the same algorithm that is used to
encrypt
stored procedures, triggers and views.
create table users(
usernamed varchar(25),
pass varbinary(16)) -- 8 characters password
insert into users values('user1', encrypt('123'))
insert into users values('user2', encrypt('456'))
insert into users values('user3', encrypt('789'))
select * from users
usernamed pass
-- --
user1 0x3E16CDED63C6
user2 0x3B3AB5941B3C
user3 0x383C6F64E6D0
(3 row(s) affected)
select * from users where pass = encrypt('123')
usernamed pass
-- --
user1 0x3E16CDED63C6
(1 row(s) affected)
Remember that encrypt is an undocumented and unsupported function. As
such
data encrypted with the function might become corrupted in a migration
to a
later version of SQL Server.
The encrypt function changed from SQL Server 6.x to
SQL Server 7.0, and individuals that used the encrypt function to
encrypt
data in SQL Server 6.x had such problems.
-->>>
I find that the above has some info w.r.t Encrypt. But it is for SQL
Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
Thx in advans,
Karthik Balaguru|||Hi,
Please check:
How to: Encrypt a Column of Data from SQL 2005 BOL
Also:
See the following links for information on how to search encrypted
data:
http://blogs.msdn.com/lcris/archive.../22/506931.aspx
http://blogs.msdn.com/raulga/archiv.../11/549754.aspx
HTH,
Dinesh|||On Jul 25, 12:59 pm, dinu_b...@.hotmail.com wrote:
> Hi,
> Please check:
> How to: Encrypt a Column of Data from SQL 2005 BOL
> Also:
> See the following links for information on how to search encrypted
> data:
> http://blogs.msdn.com/lcris/archive.../11/549754.aspx
> HTH,
> Dinesh
Thx Dinesh, Looks interesting.
Karthik Balaguru

Encrypting a Single Row/Column

Hi,
Is there any way to encrypt and decrypt a Single Row/Column in SQL
server 2000 and SQL server 2005.
I do not find any info w.r.t it in the documentation.
Thx in advans,
Karthik Balaguru
On Jul 25, 12:04 pm, karthikbalaguru <karthikbalagur...@.gmail.com>
wrote:
> Hi,
> Is there any way to encrypt and decrypt a Single Row/Column in SQL
> server 2000 and SQL server 2005.
> I do not find any info w.r.t it in the documentation.
> Thx in advans,
> Karthik Balaguru
Hi,
I found the below link having some info w.r.t Encrypt. But it is for
SQL Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
http://groups.google.co.in/group/microsoft.public.sqlserver.programming/browse_thread/thread/fd4a204efd4a69a8/?hl=en#
Thx in advans,
Karthik Balaguru
-->>>>>
You can use the encrypt function even if you don't know what's the
encryption algorithm.
It will encrypt your data using the same algorithm that is used to
encrypt
stored procedures, triggers and views.
create table users(
usernamed varchar(25),
pass varbinary(16)) -- 8 characters password
insert into users values('user1', encrypt('123'))
insert into users values('user2', encrypt('456'))
insert into users values('user3', encrypt('789'))
select * from users
usernamed pass
-- --
user1 0x3E16CDED63C6
user2 0x3B3AB5941B3C
user3 0x383C6F64E6D0
(3 row(s) affected)
select * from users where pass = encrypt('123')
usernamed pass
-- --
user1 0x3E16CDED63C6
(1 row(s) affected)
Remember that encrypt is an undocumented and unsupported function. As
such
data encrypted with the function might become corrupted in a migration
to a
later version of SQL Server.
The encrypt function changed from SQL Server 6.x to
SQL Server 7.0, and individuals that used the encrypt function to
encrypt
data in SQL Server 6.x had such problems.
-->>>
I find that the above has some info w.r.t Encrypt. But it is for SQL
Server 6x & SQL Server 7x.
What about Encryption in SQL Server 2000 & SQL Server 2005. ? Any
ideas / info ?
Thx in advans,
Karthik Balaguru
|||Hi,
Please check:
How to: Encrypt a Column of Data from SQL 2005 BOL
Also:
See the following links for information on how to search encrypted
data:
http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx
http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx
HTH,
Dinesh
|||On Jul 25, 12:59 pm, dinu_b...@.hotmail.com wrote:
> Hi,
> Please check:
> How to: Encrypt a Column of Data from SQL 2005 BOL
> Also:
> See the following links for information on how to search encrypted
> data:
> http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspxhttp://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx
> HTH,
> Dinesh
Thx Dinesh, Looks interesting.
Karthik Balaguru

encrypting a field in SQL Server 2000

Hi,

I have a SSN field that I need to encrypt. Only persons who wants to get information about a user should be able to decrypt that field. What is the best way to encrypt and decrypt the ssn field? Thanks

Maybe you can write a UDF which uses some encryption algorithm to generate the encrypted data from SSN and something known by only the user and DBA (something like password salt). However there're some available softwares, for example:

http://www.appsecinc.com/products/dbencrypt/mssql/

orhttp://www.activecrypt.com/products.html

|||

I wouldn't use SQL Server to do the encrypt/decrypt for a number of reasons (It's unencrypted when sent from the web server to the database server, it's viewable with trace utilities like SQL Profiler, and you would either need to store the encrption key in the database unencrypted, or send it and then the encryption/decryption key is sent unencrypted).

I would look into the encryption stuff that .NET has built in to start.

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.

Encrypt/Decrypt SQL Server 2005 data files

We are trying to encrypt/decrypt a SQL Server 2005 database file.
It is my understanding that you can encrypt the main database, but not
its log file. The database file was successfully encrypted, but SQL
Server failed to decrypt it on opening after a many minutes delay. The
database was subsequently decrypted with a manual command, but the
database had been damaged and couldn't be re-opened. It had to be
deleted and restored.
It appears that there is no practical way to use an encrypted SQL
database because of apparent glitches and the extremely slow decryption
process.
We have considered backing up the database, encrypting the backup copy,
deleting the database from the SQL directory on shutdown and then
restoring it on startup. Another alternative is to store the data on
removable media.
I would greatly appreciate a suggestion as to how to best protect the
data. We use SecuriKey to protect OS system startup. This works, but it
doesn't protect the data if, for example, the hard drive is moved to
another computer.
I have read the following article:
[url]http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx[/url
]
Thank you very much.
Robert RobinsonHave you looked at using Windows Encrypted File System? That's the supported
way of protecting your data at the filesystem level. There are a few things
to be careful with paticularly with login/permissions management when
encrypting the folder but it's not rocket science (and well document in
msdn/technet).
As for losing the drive, well, not much you can do there really. Even if you
encrypt the filesystem, that generally just delays the would-be thief. When
you lose the hardware, pretty much all bets are off. If you're thinking of
notebooks, you can implement both EFS and secure the hard disk with a
password (go to setup when you boot). That makes is REALLY hard to get
through and will probably buy you enough time to initiate all kinds of
remedial defense actions (e.g. place credit alerts, cancel credit cards,
update resume & post on monster.com, etc...) before they get to your data.
joe.
"Robert Robinson" <robbiex@.bellsouth.net> wrote in message
news:e9fdHUdDHHA.3660@.TK2MSFTNGP06.phx.gbl...
> We are trying to encrypt/decrypt a SQL Server 2005 database file.
> It is my understanding that you can encrypt the main database, but not its
> log file. The database file was successfully encrypted, but SQL Server
> failed to decrypt it on opening after a many minutes delay. The database
> was subsequently decrypted with a manual command, but the database had
> been damaged and couldn't be re-opened. It had to be deleted and restored.
> It appears that there is no practical way to use an encrypted SQL database
> because of apparent glitches and the extremely slow decryption process.
> We have considered backing up the database, encrypting the backup copy,
> deleting the database from the SQL directory on shutdown and then
> restoring it on startup. Another alternative is to store the data on
> removable media.
> I would greatly appreciate a suggestion as to how to best protect the
> data. We use SecuriKey to protect OS system startup. This works, but it
> doesn't protect the data if, for example, the hard drive is moved to
> another computer.
> I have read the following article:
> [url]http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.aspx[/u
rl]
> Thank you very much.
> Robert Robinson|||Hi Joe,
Thank you very much for the reply. EFS is what we tried. There are two
unfortunate limitations. First, according to Microsoft, you cannot use
SQL if the log file is encrypted. Second, decrypt takes many minutes and
the long required time makes the technology impractical to use.
I agree that there is no absolute way to prevent access to data once an
expert has physical possession of a computer or a hard drive.
SecuriKey does work as advertised. There are ways to circumvent the
technology, but it provides some protection.
Robbie|||> Thank you very much for the reply. EFS is what we tried. There are two
> unfortunate limitations. First, according to Microsoft, you cannot use SQL
> if the log file is encrypted. Second, decrypt takes many minutes and the
> long required time makes the technology impractical to use.
> I agree that there is no absolute way to prevent access to data once an
> expert has physical possession of a computer or a hard drive.
> SecuriKey does work as advertised. There are ways to circumvent the
> technology, but it provides some protection.
Maybe you can encrypt just the snsitive part of the data? Try to look at the
EncryptByKey and other encryption functions in BOL. Together with carefully
set NTFS permissions and encrypted backup you might get what you need.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Hi Dejan,
Thank you very much for the suggestions.
Robbie
Dejan Sarka wrote:
> Maybe you can encrypt just the snsitive part of the data? Try to look at t
he
> EncryptByKey and other encryption functions in BOL. Together with carefull
y
> set NTFS permissions and encrypted backup you might get what you need.
>|||We decided on the following to provide a reasonable level of protection.
First, computer access is limited by using the SecuriKey.
SQL database files are protected as follows:
Setup
1. The SQL databases to be protected are backed up and are then deleted
from SQL Server.
2. PGP Desktop 9.5 is used to create a new Virtual Disk.
3. This disk is mounted.
4. A new SQL database is created with its data and log files assigned to
be resident on the virtual disk.
5. The data are restored from the backup file.
Start-Up
1. The virtual disk is mounted automatically on start-up or under manual
or programmatic control.
2. A PGP passphrase is entered manually.
3. The SQL database is attached.
Shut-Down
1. The SQL database is detached.
2. The virtual disk is unmounted under manual or programmatic control.
Note that the attach/detach steps are required because SQL Server locks
access to the Log files and the virtual disk cannot not be unmounted
until this lock is released.|||"Robert Robinson" <robbiex@.bellsouth.net> wrote in message
news:uibht4IEHHA.3600@.TK2MSFTNGP06.phx.gbl...
> We decided on the following to provide a reasonable level of protection.
> First, computer access is limited by using the SecuriKey.
> SQL database files are protected as follows:
> Setup
> 1. The SQL databases to be protected are backed up and are then deleted
> from SQL Server.
Are you concerned that fragments of unencrypted data might be lying around
on the storage device even after deletion? Just curious. Thanks.|||Hi Mike,
We are interested in providing a reasonable level of protection for
laptop data. The backup file is created on a server and doesn't have to
be installed on a laptop. The data can be transferred by LAN or
removable media. Your point is, however, well taken. There is no way to
absolutely delete data from a hard drive short of physical destruction
of the platters.
On a slightly different subject, we have run into some interesting
issues involved in using SQL Server data files that are resident in an
encrypted disk volume.
SQL Server locks a database's log file and it is not possible to unmount
a "secure" volume without first releasing this lock. The lock can be
released by an ALTER DATABASE <its name> SET OFFLINE followed by
sp_detach_db.
The database is attached by a SQL script as follows:
Use Master
GO
EXEC sp_attach_db @.dbname = N'database name',
@.filename1 = N'S:\SQLServerData\database name.mdf',
@.filename2 = N'S:\SQLServerData\database name_log.ldf'
GO
The script is executed by:
Shell("sqlcmd -i C:\AttachDetach.sql -U <owner name> -P
<password> -s <server name>")
One interesting glitch is that the above command fails if the owner
name/password precedes the command file.
Another issue is that one needs to know what is shutting down the
application program that is accessing the database. For example, it
might be a normal program exit, a logoff, a battery low warning, or a
system suspend or shutdown.
We had to do some hunting to find the appropriate events. The following
are helpful: Microsoft.Win32.SystemEvents.SessionEnding,
Microsoft.Win32.SystemEvents.PowerModeChanged
and an interesting control called sysinfo.ocx.
Robbie

Encrypt/decrypt MS SQL 2005

hi guyz!! is it posible to ecnrypt data everytime i insert it to a table and decrypt it everytime I select it using the MS SQL 2005 alone?

like for example i have this query statement below

insert username,password users values ('daimous','my_password')

what i want is every time i insert a value to the password column that value should be encrypted first.

select username,password from users

everytime i select the value of the password column should decrypted.
Thanks in advance!!!Passwords should never need to be decrypted.

The standard method of handling them is to store the encrypted value in the database. When a user logs in, the password they submit is encrypted using the same algorithm and compared to the stored encrypted value.

Passwords can thus use one-way encryption algorithms, which are more secure than two-way algorithms.

If you would like a function for one-way password encryption, I can post one for you.|||SQL 2005 comes with two functions "EncryptByKey" and "DecryptByKey". If you have BOL installed on your machine, look over this article:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2d76cda0adc.htm|||how can i check is BOL is installed in my sql server?|||Query Analyzer/help.

And it should be in your Windows Start menu as well.|||Does SQL Express 2005 edition has it?|||Dunno. But here it is on Microsoft's site, and I'm sure you can download it somewhere. I downloaded 2005's Books Online.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_4z51.asp|||I've already got an idea on how to encrypt data by using the EncryptByKey but when i try to decrpyt it using the DecryptByKey it doesn't return the original text. I've already posted that problem HERE (http://www.dbforums.com/showthread.php?t=1606236). COuld anyone help me on this..thanks!!!

encrypt/decrypt data

We like to secure datas.
Only a few people are autorized to read this information, but today, these informations are readable with a simple query with a query analyzer for exemple.

I'd like to encrypt datas with reversible function in one field of a table

Is there a function able to do this kind of work in SQLServer V7 or 2000 ?I would think that you would be better off using native SQL security to restrict access to the data rather than attempting to encrypt it. I personally prefer to write stored procs that "wrap" my tables and then I grant exec privileges on the stored procs to the appropriate roles. This way, I don't have to grant select on the underlying tables to anyone.

Alternatively, you can grant select privileges to specific roles based on need. You may also grant the select privilege down to a particular column(s) of data.

Note that you could also get an SSL certificate and force encryption between the client and the host, but I'm not certain that this will help in your circumstance.

hmscott

We like to secure datas.
Only a few people are autorized to read this information, but today, these informations are readable with a simple query with a query analyzer for exemple.

I'd like to encrypt datas with reversible function in one field of a table

Is there a function able to do this kind of work in SQLServer V7 or 2000 ?

Encrypt/ decrypt network traffic.

I can sucessfully encrypt/ decrypt 1 column, but Im under the impression there is also a way to encrypt the data being sent over the network by using a certificate? I can find lots of info, but no starting point or clear cut instructions. Could someone please assist?
TIA, cfrgo to books online. hit the search tab and type in ssl.|||Perfect, thanks!

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/e1e55519-97ec-4404-81ef-881da3b42006.htm

encrypt/ decrypt fields or VB function "StrReverse".

I need to encrypt some fields on insert/ update and decrypt on select.
I have developed a function in visual basic but I need it to be a SQL Server
function. Is there any built-in functions availables to encrypt/ decrypt
fields or a substitution to visual basic's function "StrReverse" .
Kind regards
Khurram ButtThe REVERSE function in T-SQL is equivalent to the StrReverse function
from Visual Basic.
In SQL Server 2000, there are some undocumented functions for one-way
encryption of passwords (and other similar data): pwdencrypt and
pwdcompare. See this page (for example):
http://weblogs.asp.net/bdesmond/arc...8/15/24177.aspx
In SQL Server 2005, things are much better: there are functions for
encryption and decryption (using symmetric or asymmetric keys) and also
for digital signing.
Razvan|||No there are no such built-in functions in SQL Server 2000, probably you
have to handle this in your VB application or by creating extended stored
procedures.
"Khurram Shahzad" <Khurram.Shahzad@.360training.com> wrote in message
news:eFFHjbXrFHA.1236@.TK2MSFTNGP10.phx.gbl...
>I need to encrypt some fields on insert/ update and decrypt on select.
> I have developed a function in visual basic but I need it to be a SQL
> Server function. Is there any built-in functions availables to encrypt/
> decrypt fields or a substitution to visual basic's function "StrReverse" .
> Kind regards
> Khurram Butt
>

Encrypt sproc still returns NULL's to non DBO's.

If someone would try out my script below I'd really appreciate it. Whenever
I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite the
fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo'"
in the execution. All ideas are welcomed.
TIA, ChrisR
USE [AdventureWorks];
GO
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'vato'
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
create procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OF
F
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
GRANT IMPERSONATE ON USER:: dbo TO test;
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/Hi Chris
I could not get you sample to produce the effect you say, but then I changed
the procedure to open/close the keys. You should have the keys open for as
short a time as possible
CREATE PROCEDURE getDecryptedIDNumber
WITH EXEC AS OWBER
AS
OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID Numb
er],
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS [Decrypted
ID
Number]
FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_01;
GO
If this does not work you may want to post in
microsoft.public.sqlserver.security
A good source for encryption information is
http://blogs.msdn.com/lcris/archive/category/10357.aspx
http://blogs.msdn.com/lcris/archive.../13/512829.aspx will dop what you
but signs the procedure instead.
John
"ChrisR" wrote:

> If someone would try out my script below I'd really appreciate it. Wheneve
r
> I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite t
he
> fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo
'"
> in the execution. All ideas are welcomed.
> TIA, ChrisR
>
> USE [AdventureWorks];
> GO
> IF NOT EXISTS
> (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> CREATE MASTER KEY ENCRYPTION BY
> PASSWORD =
> 'vato'
> GO
> OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
> CREATE CERTIFICATE HumanResources037
> WITH SUBJECT = 'Employee Social Security Numbers';
> GO
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> -- Create a column in which to store the encrypted data
> ALTER TABLE HumanResources.Employee
> ADD EncryptedNationalIDNumber varbinary(128);
> GO
> -- Open the symmetric key with which to encrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> -- Encrypt the value in column NationalIDNumber with symmetric
> -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> UPDATE HumanResources.Employee
> SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> NationalIDNumber);
> GO
> -- Verify the encryption.
> -- First, open the symmetric key with which to decrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> GO
> -- Now list the original ID, the encrypted ID, and the
> -- decrypted ciphertext. If the decryption worked, the original
> -- and the decrypted ID will match.
> create procedure getDecryptedIDNumber
> with exec as owner
> as
> SELECT NationalIDNumber, EncryptedNationalIDNumber
> AS "Encrypted ID Number",
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> AS "Decrypted ID Number"
> FROM HumanResources.Employee;
> GO
> /*works for me, shows the decrypted data*/
> exec getDecryptedIDNumber
> USE [master]
> GO
> CREATE LOGIN [test] WITH PASSWORD=N'test',
> DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=
OFF
> GO
> USE [AdventureWorks]
> GO
> CREATE USER [test] FOR LOGIN [test]
> GO
> use [AdventureWorks]
> GO
> GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
> GO
> GRANT IMPERSONATE ON USER:: dbo TO test;
> GO
> /*Now, open up a "file/new/DB Engine Query" and login with the test login*
/
> exec as user = 'dbo'
> exec getDecryptedIDNumber
> /*This returns NULL values where it should show the decrypted data*/
>
>|||Good enough, thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BD1F94F-E0F6-44EB-83FE-2C930BF1FF04@.microsoft.com...
> Hi Chris
> I could not get you sample to produce the effect you say, but then I
changed
> the procedure to open/close the keys. You should have the keys open for as
> short a time as possible
>
> CREATE PROCEDURE getDecryptedIDNumber
> WITH EXEC AS OWBER
> AS
> OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
> SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID
Number],
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS [Decrypted[/vbco
l]
ID[vbcol=seagreen]
> Number]
> FROM HumanResources.Employee;
> CLOSE SYMMETRIC KEY SSN_Key_01;
> GO
> If this does not work you may want to post in
> microsoft.public.sqlserver.security
> A good source for encryption information is
> http://blogs.msdn.com/lcris/archive/category/10357.aspx
> http://blogs.msdn.com/lcris/archive.../13/512829.aspx will dop what
you[vbcol=seagreen]
> but signs the procedure instead.
>
> John
> "ChrisR" wrote:
>
Whenever[vbcol=seagreen]
the[vbcol=seagreen]
'dbo'"[vbcol=seagreen]
CHECK_POLICY=OFF[vbcol=seagreen]
login*/[vbcol=seagreen]

Encrypt sproc still returns NULL's to non DBO's.

If someone would try out my script below I'd really appreciate it. Whenever
I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite the
fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo'"
in the execution. All ideas are welcomed.
TIA, ChrisR
USE [AdventureWorks];
GO
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'vato'
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
create procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
GRANT IMPERSONATE ON USER:: dbo TO test;
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/Good enough, thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BD1F94F-E0F6-44EB-83FE-2C930BF1FF04@.microsoft.com...
> Hi Chris
> I could not get you sample to produce the effect you say, but then I
changed
> the procedure to open/close the keys. You should have the keys open for as
> short a time as possible
>
> CREATE PROCEDURE getDecryptedIDNumber
> WITH EXEC AS OWBER
> AS
> OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
> SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID
Number],
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS [Decrypted
ID
> Number]
> FROM HumanResources.Employee;
> CLOSE SYMMETRIC KEY SSN_Key_01;
> GO
> If this does not work you may want to post in
> microsoft.public.sqlserver.security
> A good source for encryption information is
> http://blogs.msdn.com/lcris/archive/category/10357.aspx
> http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx will dop what
you
> but signs the procedure instead.
>
> John
> "ChrisR" wrote:
> > If someone would try out my script below I'd really appreciate it.
Whenever
> > I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite
the
> > fact that I use "with exec as owner" in the sproc and "exec as user ='dbo'"
> > in the execution. All ideas are welcomed.
> >
> > TIA, ChrisR
> >
> >
> > USE [AdventureWorks];
> > GO
> >
> > IF NOT EXISTS
> > (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> > CREATE MASTER KEY ENCRYPTION BY
> > PASSWORD => > 'vato'
> > GO
> >
> > OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
> >
> > CREATE CERTIFICATE HumanResources037
> > WITH SUBJECT = 'Employee Social Security Numbers';
> > GO
> >
> > CREATE SYMMETRIC KEY SSN_Key_01
> > WITH ALGORITHM = DES
> > ENCRYPTION BY CERTIFICATE HumanResources037;
> > GO
> >
> > -- Create a column in which to store the encrypted data
> > ALTER TABLE HumanResources.Employee
> > ADD EncryptedNationalIDNumber varbinary(128);
> > GO
> >
> > -- Open the symmetric key with which to encrypt the data
> > OPEN SYMMETRIC KEY SSN_Key_01
> > DECRYPTION BY CERTIFICATE HumanResources037;
> >
> > -- Encrypt the value in column NationalIDNumber with symmetric
> > -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> > UPDATE HumanResources.Employee
> > SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> > NationalIDNumber);
> > GO
> >
> > -- Verify the encryption.
> > -- First, open the symmetric key with which to decrypt the data
> > OPEN SYMMETRIC KEY SSN_Key_01
> > DECRYPTION BY CERTIFICATE HumanResources037;
> > GO
> >
> > -- Now list the original ID, the encrypted ID, and the
> > -- decrypted ciphertext. If the decryption worked, the original
> > -- and the decrypted ID will match.
> >
> > create procedure getDecryptedIDNumber
> > with exec as owner
> > as
> > SELECT NationalIDNumber, EncryptedNationalIDNumber
> > AS "Encrypted ID Number",
> > CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> > AS "Decrypted ID Number"
> > FROM HumanResources.Employee;
> > GO
> >
> > /*works for me, shows the decrypted data*/
> >
> > exec getDecryptedIDNumber
> >
> > USE [master]
> > GO
> >
> > CREATE LOGIN [test] WITH PASSWORD=N'test',
> > DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
> > GO
> >
> > USE [AdventureWorks]
> > GO
> >
> > CREATE USER [test] FOR LOGIN [test]
> > GO
> >
> > use [AdventureWorks]
> > GO
> >
> > GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
> > GO
> >
> > GRANT IMPERSONATE ON USER:: dbo TO test;
> > GO
> >
> > /*Now, open up a "file/new/DB Engine Query" and login with the test
login*/
> > exec as user = 'dbo'
> > exec getDecryptedIDNumber
> >
> > /*This returns NULL values where it should show the decrypted data*/
> >
> >
> >

Encrypt sproc still returns NULL's to non DBO's.

If someone would try out my script below I'd really appreciate it. Whenever
I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite the
fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo'"
in the execution. All ideas are welcomed.
TIA, ChrisR
USE [AdventureWorks];
GO
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'vato'
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
create procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
GRANT IMPERSONATE ON USER:: dbo TO test;
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/
Hi Chris
I could not get you sample to produce the effect you say, but then I changed
the procedure to open/close the keys. You should have the keys open for as
short a time as possible
CREATE PROCEDURE getDecryptedIDNumber
WITH EXEC AS OWBER
AS
OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID Number],
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS [Decrypted ID
Number]
FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_01;
GO
If this does not work you may want to post in
microsoft.public.sqlserver.security
A good source for encryption information is
http://blogs.msdn.com/lcris/archive/category/10357.aspx
http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx will dop what you
but signs the procedure instead.
John
"ChrisR" wrote:

> If someone would try out my script below I'd really appreciate it. Whenever
> I run a decrypt sproc as a non DBO, it doesn't decrypt the data, despite the
> fact that I use "with exec as owner" in the sproc and "exec as user = 'dbo'"
> in the execution. All ideas are welcomed.
> TIA, ChrisR
>
> USE [AdventureWorks];
> GO
> IF NOT EXISTS
> (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> CREATE MASTER KEY ENCRYPTION BY
> PASSWORD =
> 'vato'
> GO
> OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'
> CREATE CERTIFICATE HumanResources037
> WITH SUBJECT = 'Employee Social Security Numbers';
> GO
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> -- Create a column in which to store the encrypted data
> ALTER TABLE HumanResources.Employee
> ADD EncryptedNationalIDNumber varbinary(128);
> GO
> -- Open the symmetric key with which to encrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> -- Encrypt the value in column NationalIDNumber with symmetric
> -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> UPDATE HumanResources.Employee
> SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> NationalIDNumber);
> GO
> -- Verify the encryption.
> -- First, open the symmetric key with which to decrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> GO
> -- Now list the original ID, the encrypted ID, and the
> -- decrypted ciphertext. If the decryption worked, the original
> -- and the decrypted ID will match.
> create procedure getDecryptedIDNumber
> with exec as owner
> as
> SELECT NationalIDNumber, EncryptedNationalIDNumber
> AS "Encrypted ID Number",
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> AS "Decrypted ID Number"
> FROM HumanResources.Employee;
> GO
> /*works for me, shows the decrypted data*/
> exec getDecryptedIDNumber
> USE [master]
> GO
> CREATE LOGIN [test] WITH PASSWORD=N'test',
> DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
> GO
> USE [AdventureWorks]
> GO
> CREATE USER [test] FOR LOGIN [test]
> GO
> use [AdventureWorks]
> GO
> GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
> GO
> GRANT IMPERSONATE ON USER:: dbo TO test;
> GO
> /*Now, open up a "file/new/DB Engine Query" and login with the test login*/
> exec as user = 'dbo'
> exec getDecryptedIDNumber
> /*This returns NULL values where it should show the decrypted data*/
>
>
|||Good enough, thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BD1F94F-E0F6-44EB-83FE-2C930BF1FF04@.microsoft.com...
> Hi Chris
> I could not get you sample to produce the effect you say, but then I
changed
> the procedure to open/close the keys. You should have the keys open for as
> short a time as possible
>
> CREATE PROCEDURE getDecryptedIDNumber
> WITH EXEC AS OWBER
> AS
> OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
> SELECT NationalIDNumber, EncryptedNationalIDNumber AS [Encrypted ID
Number],
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS [Decrypted
ID
> Number]
> FROM HumanResources.Employee;
> CLOSE SYMMETRIC KEY SSN_Key_01;
> GO
> If this does not work you may want to post in
> microsoft.public.sqlserver.security
> A good source for encryption information is
> http://blogs.msdn.com/lcris/archive/category/10357.aspx
> http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx will dop what
you[vbcol=seagreen]
> but signs the procedure instead.
>
> John
> "ChrisR" wrote:
Whenever[vbcol=seagreen]
the[vbcol=seagreen]
'dbo'"[vbcol=seagreen]
CHECK_POLICY=OFF[vbcol=seagreen]
login*/[vbcol=seagreen]

encrypt password

Hi,

I would like to encrypt and decrypt password in my own tables

select stuff

from mytable

where password = decrypt('ackdo$$y')

How can I do this? Is there any decrypt or crypt function?

thanks,

Are you using SQL Server 2005 ? Then you will have the option to use the Encryptby functions, if you don′t want to use them you can implement your own using CLR functions. If you are using SQL 2k you either will have to write a XP for more sophisticated encryption, for low encryption you could use a stored procedure or function, if you don′t want to do this on the server, you will have to do it on the client.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi.

I am using sql 2005. Therefore how can I use this encrypt or decrypt function?

cheers,

Encrypt data in SQL Server

Does SQL Server has any build-in function for encrypt & decrypt database
data?
Grey,
SQL Server 2000 does not support database data encryption as such. There are
third party products available: see, for example, DbEncrypt or XP_Crypt. SQL
Server 2005 will have column-level encryption; you can read more about it by
searching on the Web.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Grey" <erickwyum@.i-cable.com> wrote in message
news:%235%23$6AsnEHA.2900@.TK2MSFTNGP09.phx.gbl...
> Does SQL Server has any build-in function for encrypt & decrypt database
> data?
>

Encrypt data in SQL Server

Does SQL Server has any build-in function for encrypt & decrypt database
data'Grey,
SQL Server 2000 does not support database data encryption as such. There are
third party products available: see, for example, DbEncrypt or XP_Crypt. SQL
Server 2005 will have column-level encryption; you can read more about it by
searching on the Web.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Grey" <erickwyum@.i-cable.com> wrote in message
news:%235%23$6AsnEHA.2900@.TK2MSFTNGP09.phx.gbl...
> Does SQL Server has any build-in function for encrypt & decrypt database
> data'
>

Encrypt Data

Hi,

In SQL2000 i need to Encryptdata when I export data using DTS.LikeWise I should when i import data I should authenticate that user and decrypt that.

Can any one help?

Thanks,

Karthik

vgvKarthik wrote:

Hi,

In SQL2000 i need to Encryptdata when I export data using DTS.LikeWise I should when i import data I should authenticate that user and decrypt that.

Can any one help?

Thanks,

Karthik

Yeah. Try a different forum. This has got nothing to do with SSIS.

-Jamie