Hi Guys,
We're currently running SQL Server 2000. We have just been instructed to
ensure that our data is encrypted whenever possible and I was wondering if
any of you could give me some advice about my various options.
I know SQL Server 2005 supports encryption, however, we have a huge amount
of legacy (hard coded classic ASP sql statements) that as far as I can see,
would all need to be updated in order to utilize the native encryption. Is
this the case? Is there a way to encrypt/decrypt a table when it is stored
on the file system as opposed to particular values within it?
We have also looked at the possibility of using EFS for the data storage
folders - has anyone used this before? Is it possible? Any advice on the
implications of this?
The best case scinario would obviously be to have a solution that does not
require us to make any changes to our existing code, but to ensure that the
data is held securely in the event of theft etc.
Any ideas would be really appreciated.
Kind Regards,
ChrisFor column-level encryption, there are lots of 3rd party options available.
Here's one with the perfect price:
http://www.sqlservercentral.com/col...oolkitpart1.asp
I understand others here have used EFS and other tools for file-level
encryption (haven't done so myself), but this might be a good starting
point: http://msdn2.microsoft.com/en-us/library/aa302434.aspx
"Chris Evans" <ChrisEvans@.discussions.microsoft.com> wrote in message
news:F1C1958E-7086-49E1-BCD0-952743FA0F0C@.microsoft.com...
> Hi Guys,
> We're currently running SQL Server 2000. We have just been instructed to
> ensure that our data is encrypted whenever possible and I was wondering if
> any of you could give me some advice about my various options.
> I know SQL Server 2005 supports encryption, however, we have a huge amount
> of legacy (hard coded classic ASP sql statements) that as far as I can
> see,
> would all need to be updated in order to utilize the native encryption.
> Is
> this the case? Is there a way to encrypt/decrypt a table when it is
> stored
> on the file system as opposed to particular values within it?
> We have also looked at the possibility of using EFS for the data storage
> folders - has anyone used this before? Is it possible? Any advice on the
> implications of this?
> The best case scinario would obviously be to have a solution that does not
> require us to make any changes to our existing code, but to ensure that
> the
> data is held securely in the event of theft etc.
> Any ideas would be really appreciated.
> Kind Regards,
>
> Chris
Showing posts with label whenever. Show all posts
Showing posts with label whenever. Show all posts
Thursday, March 22, 2012
Sunday, March 11, 2012
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*/
> >
> >
> >
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*/
> >
> >
> >
Friday, February 17, 2012
Empty rows in excel data sheet used in DTS
Hi Everyone,
I am using a DTS package where one of the inputs is an Excel Sheet. Actually this sheet is updated manually whenever required i.e once a week or sometimes once a month, but the DTS package runs everyday.
Whenever new rows are added or deleted manually in the excel sheet, empty rows are showed in the sheet after the last row of data. This hinders the DTS package, because the destination table to which the data in the Excel sheet is sent has Primary keys in it.
Can anyone suggest me how to avoid getting the empty spaces in the excel sheet.
Thanks in advance.
Regards,
kalyanExcel has a funny habit of recognising that there is data present when there really isn't. The easiest solution is when deleting records don't just press the delete key, actually right click the mouse and choose delete from the available options. It may even be better to run some macro inside the document that deletes all records below the last record in this manner.|||Hi SJP,
Thanks for the prompt reply. I think to run a macro to delete all records below the last record would be the most feasible solution.
Thanks again.
Regards,
kalyan|||The simple solution I applied was to delete all the named ranges in the Excel worksheet (From the Excel menu: Insert > Name > Define. It seems that after rows are deleted in the Excel sheet, the named ranges do not get updated. After deleting the named ranges, it was easy to Import the data.
I am using a DTS package where one of the inputs is an Excel Sheet. Actually this sheet is updated manually whenever required i.e once a week or sometimes once a month, but the DTS package runs everyday.
Whenever new rows are added or deleted manually in the excel sheet, empty rows are showed in the sheet after the last row of data. This hinders the DTS package, because the destination table to which the data in the Excel sheet is sent has Primary keys in it.
Can anyone suggest me how to avoid getting the empty spaces in the excel sheet.
Thanks in advance.
Regards,
kalyanExcel has a funny habit of recognising that there is data present when there really isn't. The easiest solution is when deleting records don't just press the delete key, actually right click the mouse and choose delete from the available options. It may even be better to run some macro inside the document that deletes all records below the last record in this manner.|||Hi SJP,
Thanks for the prompt reply. I think to run a macro to delete all records below the last record would be the most feasible solution.
Thanks again.
Regards,
kalyan|||The simple solution I applied was to delete all the named ranges in the Excel worksheet (From the Excel menu: Insert > Name > Define. It seems that after rows are deleted in the Excel sheet, the named ranges do not get updated. After deleting the named ranges, it was easy to Import the data.
Subscribe to:
Posts (Atom)