Encryption

I have tried to encrypt by certificate and by symmetric key. In all cases the decryption comes back as null. Any ideas why?

I have used the code from a learnin tree course and the encryption works OK. I have also added a grant to the certificate to the login

Please post the code you used. Without it we would have to guess every possible problem.

encryption

Why doesn't this work?
CREATE TABLE [dbo].[WebSecurity](
[WebSecurityID] [int] IDENTITY(1,1) NOT NULL,
[webIdentity] [varchar](50) NOT NULL,
[webPassword_encrypted] [varbinary](256) NULL)
delete from websecurity
insert into WebSecurity (WebIdentity,webpassword_encrypted)
values('vanwagenen\WebServer',EncryptByP
assPhrase ('myphrase','mypassword'))
select webidentity, decryptByPassPhrase
('myphrase',webpassword_encrypted)from webSecurity
--
Arne Garvander
Certified Geek
Professional Data DudeHello Arne,
It did, you just didn't get the answer you expected. Try:
select webidentity, cast(decryptByPassPhrase ('myphrase',webpassword_encrypt
ed)
as varchar(255)) from webSecurity
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

EncryptByKey Function always returning null

When I use EncryptByKey function to encrypt text using AES 128 bit key, I get always null result. this is how I do the encryption:

ALTER PROCEDURE [dbo].[ProcMyProc](@.ClearText nvarchar(50))
AS
BEGIN

OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD= 'MY_Password_128';

Declare @.Temp varbinary(8000);

Set @.Temp =EncryptByKey(Key_GUID('MyKey'),@.ClearText);

close symmetric key MyKey;

select @.Temp as temp;
END

The result I get for this procedure is null. Is there something wrong with this code?

Hey Muhammad,

I tried out your code and it seems to work for me. Can you try executing your script outside of the proc? You can add in "select * from sys.openkeys" after your open statement just to check to see if the key was actually opened.

Sung

|||

Also, if this still doesn't work, can you let us know what OS version you are using? Is it XP, 2000, or 2003?

Thanks
Laurentiu

|||I found the cause of the problem. I was misspelling the name of the key when passing it to the Key_GUID() function. but shouldn't this give an error saying there's no key with this name instead of just returning null?|||

Hi Muhammad,

The way our built-ins work is that they return null instead of returning an error. I don't remember why exactly this is the case, but I think it has to do with backwards compatibility and parsing logic of the built-ins.

Sung

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]

Empty values in XML

Hi,
Trying to pass XML as parameter to stored procedure and get the result set.
I want to allow null values. Here is the problem: when I leave a value blank
(between the <Score> and </Score> tags) it will return 0 instead of <NULL>
... See code below.
What am I doing wrong here?
Thanks in advance!
Joost
CREATE PROCEDURE dbo.Test
AS
DECLARE @.i int
DECLARE @.ScoresXML nvarchar (4000)
SET @.ScoresXML = '<Scores><Score><ID>1</ID><Result></Result></Score></Scores>'
EXEC sp_xml_preparedocument @.i OUTPUT, @.ScoresXML
SELECT
ID,
Result
FROM
OPENXML (@.i, '/Scores/Score', 2)
WITH
(IDint,
Result float)
EXEC sp_xml_removedocument @.i
Hello JoostK,
SQL Server 2000 or SQL Server 2005?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||SQL 2000
Thanks,
Joost
"Kent Tegels" <ktegels@.develop.com> schreef in bericht
news:b87ad741c6fb8c81b916f5cc940@.news.microsoft.co m...
> Hello JoostK,
> SQL Server 2000 or SQL Server 2005?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Hi Kent,
Using SQL2000
Thanks, Joost
"Kent Tegels" wrote:

> Hello JoostK,
> SQL Server 2000 or SQL Server 2005?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
|||Hello JoostK,

> Hi,
> Trying to pass XML as parameter to stored procedure and get the result
> set. I want to allow null values. Here is the problem: when I leave a
> value blank (between the <Score> and </Score> tags) it will return 0
> instead of <NULL> ... See code below.
> What am I doing wrong here?
In your table, you're asking "what's the value of the Score element", which
isn't null because it does exist. What you want is "what's the value of
the text node of the element Score". To fix this, all you need do is write
your table as:
WITH
(IDint,
Result int 'text()')
And it should give you what you want.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Empty values in XML

Hi,
Trying to pass XML as parameter to stored procedure and get the result set.
I want to allow null values. Here is the problem: when I leave a value blank
(between the <Score> and </Score> tags) it will return 0 instead of <NULL>
... See code below.
What am I doing wrong here?
Thanks in advance!
Joost
CREATE PROCEDURE dbo.Test
AS
DECLARE @.i int
DECLARE @.ScoresXML nvarchar (4000)
SET @.ScoresXML = '<Scores><Score><ID>1</ID><Result></Result></Score></Scores
>'
EXEC sp_xml_preparedocument @.i OUTPUT, @.ScoresXML
SELECT
ID,
Result
FROM
OPENXML (@.i, '/Scores/Score', 2)
WITH
(ID int,
Result float)
EXEC sp_xml_removedocument @.iHello JoostK,
SQL Server 2000 or SQL Server 2005?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||SQL 2000
Thanks,
Joost
"Kent Tegels" <ktegels@.develop.com> schreef in bericht
news:b87ad741c6fb8c81b916f5cc940@.news.microsoft.com...
> Hello JoostK,
> SQL Server 2000 or SQL Server 2005?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hi Kent,
Using SQL2000
Thanks, Joost
"Kent Tegels" wrote:

> Hello JoostK,
> SQL Server 2000 or SQL Server 2005?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hello JoostK,

> Hi,
> Trying to pass XML as parameter to stored procedure and get the result
> set. I want to allow null values. Here is the problem: when I leave a
> value blank (between the <Score> and </Score> tags) it will return 0
> instead of <NULL> ... See code below.
> What am I doing wrong here?
In your table, you're asking "what's the value of the Score element", which
isn't null because it does exist. What you want is "what's the value of
the text node of the element Score". To fix this, all you need do is write
your table as:
WITH
(ID int,
Result int 'text()')
And it should give you what you want.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Empty <> IS NOT NULL

Hello,

I have a query that returns the appropriate values I need, however there is one field I'd like to add and utilize but my problem is I only want to use it if it contains data.

If I filter it with IS NOT NULL it returns all the records, including the empty records. The field is simply empty, and doesn't come back as NULL. If I filter it with =' ' , it shows all the records with the empty records only.

I need to do the opposite, be able to filter it only if it's not empty.

Any help would be appreciated.

What is your current query? ( And indicate the field you're talking about... including its type, I guess...)

>L<

|||

Figured it out myself.

For someone elses benefit I'll include what I had to do:

Filtered the desired field using LIKE '0%' .

The data the field actually contains is a yearcode-sequence code (eg. 07-555555).

Now the empty fields are gone and it only pulls the records if it has data.

I used this for a guide. http://msdn2.microsoft.com/en-us/library/ms179859.aspx

|||Thanks for replying, I figured it out.