Empty Column: following

Showing posts with label following. Show all posts

Monday, March 26, 2012

Encryption not supported on SQL Server - Error Message

Hi,
When I click on the MSDB node, under Stored Packages (in Object Explorer | my local server's Integration Services), I get the following error message:
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
I can, however successfully, enumerate File System packages.
I am running...
Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86)
May 24 2005 18:22:46
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
...on Windows Server 2003 SP1
Does anyone know what is causing this, and how to fix it?
Thanks,
krog
Appears to be related to the following post:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=19019
...so I need to install a certificate on my computer. Idea

Am now checking whether I can use the MakeCert utility to address this.
|||Did MakeCert resolve this - if so, would you please post the entire process - having same problem - thx|||If you installed your SQL Server 2005 as an named instance with a default SQL Server 2000 on the machine, you might need to change the SSIS configuration file to point the MSDB to the right instance. After changing the configuration file, you need to restart the SSIS service.|||To Add to Roro

Edit the config file MsDtsSrvr.ini.xml in C:\Program Files\Microsoft SQL Server\90\DTS\Binn as follwos:

=====================================
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>SQL_2K5_SERVER_NAME</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
===============================

The original value for <ServerName> is just a dot (.)|||

Thanks a lot for your replies, people! I ran into the same problem and this thread helped me saved a bunch of time!! :)

|||

Yep, agree to that, thanks for the post. Restarted SSIS and it worked a treat.

Cheers - R.

|||What was resolution? Even I am also running into same problem as I have 2 SQL instances on same server

Encryption not supported on SQL Server - Error Message

Edit the config file MsDtsSrvr.ini.xml in C:\Program Files\Microsoft SQL Server\90\DTS\Binn as follwos:

The original value for <ServerName> is just a dot (.)|||

Thanks a lot for your replies, people! I ran into the same problem and this thread helped me saved a bunch of time!! :)

|||

Yep, agree to that, thanks for the post. Restarted SSIS and it worked a treat.

Cheers - R.

|||What was resolution? Even I am also running into same problem as I have 2 SQL instances on same server

Encryption not supported on SQL Server - Error Message

Edit the config file MsDtsSrvr.ini.xml in C:\Program Files\Microsoft SQL Server\90\DTS\Binn as follwos:

The original value for <ServerName> is just a dot (.)|||

Thanks a lot for your replies, people! I ran into the same problem and this thread helped me saved a bunch of time!! :)

|||

Yep, agree to that, thanks for the post. Restarted SSIS and it worked a treat.

Cheers - R.

|||What was resolution? Even I am also running into same problem as I have 2 SQL instances on same server

Encryption not supported on SQL Server - Error Message

Edit the config file MsDtsSrvr.ini.xml in C:\Program Files\Microsoft SQL Server\90\DTS\Binn as follwos:

The original value for <ServerName> is just a dot (.)|||

Thanks a lot for your replies, people! I ran into the same problem and this thread helped me saved a bunch of time!! :)

|||

Yep, agree to that, thanks for the post. Restarted SSIS and it worked a treat.

Cheers - R.

|||What was resolution? Even I am also running into same problem as I have 2 SQL instances on same serversql

Encryption not supported on SQL Server - Error Message

Edit the config file MsDtsSrvr.ini.xml in C:\Program Files\Microsoft SQL Server\90\DTS\Binn as follwos:

The original value for <ServerName> is just a dot (.)|||

Thanks a lot for your replies, people! I ran into the same problem and this thread helped me saved a bunch of time!! :)

|||

Yep, agree to that, thanks for the post. Restarted SSIS and it worked a treat.

Cheers - R.

|||What was resolution? Even I am also running into same problem as I have 2 SQL instances on same server

Encryption Not Supported Error

When running .Net apps against a local SQL Server (2000, SP3) I get the following error:
Connection failed:
ConnectionOpen (PreLoginHandshake())
Connection failed
Encryption not supported on SQL Server
Any help will be greatly appreciated.
Thanks
Hi,
Refer
INF: How SQL Server Uses a Certificate When the Force Protocol Encryption
Option is Set On
http://support.microsoft.com/default.aspx?id=318605
HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate Server
http://support.microsoft.com/?id=276553
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Footix" <anonymous@.discussions.microsoft.com> wrote in message
news:CAD808CD-CA74-4C15-B34C-88377C0D8200@.microsoft.com...
> When running .Net apps against a local SQL Server (2000, SP3) I get the
following error:
> Connection failed:
> ConnectionOpen (PreLoginHandshake())
> Connection failed
> Encryption not supported on SQL Server
> Any help will be greatly appreciated.
> Thanks
sql

Encryption Not Supported Error

When running .Net apps against a local SQL Server (2000, SP3) I get the foll
owing error:
Connection failed:
ConnectionOpen (PreLoginHandshake())
Connection failed
Encryption not supported on SQL Server
Any help will be greatly appreciated.
ThanksHi,
Refer
INF: How SQL Server Uses a Certificate When the Force Protocol Encryption
Option is Set On
http://support.microsoft.com/default.aspx?id=318605
HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate Server
http://support.microsoft.com/?id=276553
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Footix" <anonymous@.discussions.microsoft.com> wrote in message
news:CAD808CD-CA74-4C15-B34C-88377C0D8200@.microsoft.com...
> When running .Net apps against a local SQL Server (2000, SP3) I get the
following error:
> Connection failed:
> ConnectionOpen (PreLoginHandshake())
> Connection failed
> Encryption not supported on SQL Server
> Any help will be greatly appreciated.
> Thanks

Encryption Key

I am getting the following error while configuring the reporting services on
my local machine. I cannot restore the key as I do not know the file or the
password.
Somebody please help!
ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted value
for the "LogonCred" configuration setting cannot be decrypted.
(rsFailedToDecryptConfigInformation)
at
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()Hello,
Are you installing a new version of Reporting Services (with clear
ReportServer database) or just reinstalling it with using previous version
of ReportServer database'
It looks like that you've reinstalled Reporting Services without recovering
previous encryption key.
If you don't have any copy of your previous encryption key, the only way to
make Reporting Services content available is to delete all unusable
encrypted data from ReportServer database.
Follow these steps to apply the encryption key to the report server
database:
1.. Run rskeymgmt.exe locally on the computer that hosts the report
server. You must use the -d apply argument. The following example
illustrates the argument you must specify:
rskeymgmt -d
2.. Restart Internet Information Service (IIS).
After the values are removed, you must re-specify the values as follows:
1.. Run rsconfig utility to specify a report server connection. This step
replaces the report server connection information. For more information, see
Configuring a Report Server Connection and rsconfig Utility.
2.. If you are supporting unattended report execution for reports that do
not use credentials, run rsconfig to specify the account used for this
purpose. For more information, see Configuring an Account for Unattended
Report Processing.
3.. For each report and shared data source that uses stored credentials,
you must retype the user name and password. For more information, see
Specifying Credential and Connection Information.
4.. Open and resave each subscription. Subscriptions retain residual
information about the encrypted credentials deleted during the rskeymgmt
delete operation. You can update the subscription by opening and saving it.
You do not need to modify or recreate it.
I hope this infomation will helpful.
Best Regards,
Radoslaw Lebkowski
U¿ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:D246447F-1622-4230-AC73-71D0F1073C11@.microsoft.com...
>I am getting the following error while configuring the reporting services
>on
> my local machine. I cannot restore the key as I do not know the file or
> the
> password.
> Somebody please help!
> ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted
> value
> for the "LogonCred" configuration setting cannot be decrypted.
> (rsFailedToDecryptConfigInformation)
> at
> ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject
> mo)
> at
> ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()|||Hi Rad,
The very first step in trying to delete the key is giving me the same
exception which is ReportServicesConfigUI.WMIProvider.WMIProviderException:
The encrypted value
for the "LogonCred" configuration setting cannot be decrypted.
rskeymgmt -d -i instance
This is completely new installation in 2steps.
1. Installed sql server 2005 with all other services except reporting
services as iis was not installed at that time.
2. Installed reporting services.
"Radoslaw Lebkowski" wrote:
> Hello,
> Are you installing a new version of Reporting Services (with clear
> ReportServer database) or just reinstalling it with using previous version
> of ReportServer database'
> It looks like that you've reinstalled Reporting Services without recovering
> previous encryption key.
> If you don't have any copy of your previous encryption key, the only way to
> make Reporting Services content available is to delete all unusable
> encrypted data from ReportServer database.
> Follow these steps to apply the encryption key to the report server
> database:
> 1.. Run rskeymgmt.exe locally on the computer that hosts the report
> server. You must use the -d apply argument. The following example
> illustrates the argument you must specify:
> rskeymgmt -d
> 2.. Restart Internet Information Service (IIS).
> After the values are removed, you must re-specify the values as follows:
> 1.. Run rsconfig utility to specify a report server connection. This step
> replaces the report server connection information. For more information, see
> Configuring a Report Server Connection and rsconfig Utility.
> 2.. If you are supporting unattended report execution for reports that do
> not use credentials, run rsconfig to specify the account used for this
> purpose. For more information, see Configuring an Account for Unattended
> Report Processing.
> 3.. For each report and shared data source that uses stored credentials,
> you must retype the user name and password. For more information, see
> Specifying Credential and Connection Information.
> 4.. Open and resave each subscription. Subscriptions retain residual
> information about the encrypted credentials deleted during the rskeymgmt
> delete operation. You can update the subscription by opening and saving it.
> You do not need to modify or recreate it.
> I hope this infomation will helpful.
> Best Regards,
> Radoslaw Lebkowski
>
> UÂ¿ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisaÂ³ w wiadomoÂ¶ci
> news:D246447F-1622-4230-AC73-71D0F1073C11@.microsoft.com...
> >I am getting the following error while configuring the reporting services
> >on
> > my local machine. I cannot restore the key as I do not know the file or
> > the
> > password.
> > Somebody please help!
> >
> > ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted
> > value
> > for the "LogonCred" configuration setting cannot be decrypted.
> > (rsFailedToDecryptConfigInformation)
> > at
> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject
> > mo)
> > at
> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()
>
>|||Hmm, that's a little strange situation.
Under the following link there is a very similar problem with LogonCred
decryption problem.
http://groups.google.pl/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/ec7858be1ef25e24/9f72570a671b74e7?lnk=st&q=LogonCred+decrypted&rnum=1&hl=pl#9f72570a671b74e7
Maybe you have problem with authorization a connection to ReportServer
database (bad values contained in RSReportServer.config file used to connect
to the report server database)
Try to use rsconfig utility described below
http://msdn2.microsoft.com/en-us/library/aa179654(SQL.80).aspx
Regards
Radoslaw Lebkowski
U¿ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:7BC3B705-71DF-4B48-93FE-6959E94A51FF@.microsoft.com...
> Hi Rad,
> The very first step in trying to delete the key is giving me the same
> exception which is
> ReportServicesConfigUI.WMIProvider.WMIProviderException:
> The encrypted value
> for the "LogonCred" configuration setting cannot be decrypted.
> rskeymgmt -d -i instance
> This is completely new installation in 2steps.
> 1. Installed sql server 2005 with all other services except reporting
> services as iis was not installed at that time.
> 2. Installed reporting services.
> "Radoslaw Lebkowski" wrote:
>> Hello,
>> Are you installing a new version of Reporting Services (with clear
>> ReportServer database) or just reinstalling it with using previous
>> version
>> of ReportServer database'
>> It looks like that you've reinstalled Reporting Services without
>> recovering
>> previous encryption key.
>> If you don't have any copy of your previous encryption key, the only way
>> to
>> make Reporting Services content available is to delete all unusable
>> encrypted data from ReportServer database.
>> Follow these steps to apply the encryption key to the report server
>> database:
>> 1.. Run rskeymgmt.exe locally on the computer that hosts the report
>> server. You must use the -d apply argument. The following example
>> illustrates the argument you must specify:
>> rskeymgmt -d
>> 2.. Restart Internet Information Service (IIS).
>> After the values are removed, you must re-specify the values as follows:
>> 1.. Run rsconfig utility to specify a report server connection. This
>> step
>> replaces the report server connection information. For more information,
>> see
>> Configuring a Report Server Connection and rsconfig Utility.
>> 2.. If you are supporting unattended report execution for reports that
>> do
>> not use credentials, run rsconfig to specify the account used for this
>> purpose. For more information, see Configuring an Account for Unattended
>> Report Processing.
>> 3.. For each report and shared data source that uses stored
>> credentials,
>> you must retype the user name and password. For more information, see
>> Specifying Credential and Connection Information.
>> 4.. Open and resave each subscription. Subscriptions retain residual
>> information about the encrypted credentials deleted during the rskeymgmt
>> delete operation. You can update the subscription by opening and saving
>> it.
>> You do not need to modify or recreate it.
>> I hope this infomation will helpful.
>> Best Regards,
>> Radoslaw Lebkowski
>>
>> U?ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisa3 w
>> wiadomo?ci
>> news:D246447F-1622-4230-AC73-71D0F1073C11@.microsoft.com...
>> >I am getting the following error while configuring the reporting
>> >services
>> >on
>> > my local machine. I cannot restore the key as I do not know the file
>> > or
>> > the
>> > password.
>> > Somebody please help!
>> >
>> > ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted
>> > value
>> > for the "LogonCred" configuration setting cannot be decrypted.
>> > (rsFailedToDecryptConfigInformation)
>> > at
>> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject
>> > mo)
>> > at
>> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()
>>

Encryption Example

I am running through a great 2005 Encryption example at the following link:
http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx
I keep running into one problem though. When I execute the following at
this link
"open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
I get the following error
"Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'BY'."
Anybody have any idea what is going on?Below code (taken from Books Online) gives me a "proper" error message:
OPEN SYMMETRIC KEY SymKeyMarketing3
DECRYPTION BY CERTIFICATE MarketingCert9;
Server: Msg 15151, Level 16, State 1, Line 1
Cannot find the symmetric key 'SymKeyMarketing3', because it does not exist or you do not have
permission.
Perhaps the compatibility level for your database is lower than 90?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>I am running through a great 2005 Encryption example at the following link:
> http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx
> I keep running into one problem though. When I execute the following at
> this link
> "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> I get the following error
> "Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'BY'."
> Anybody have any idea what is going on?
>
>|||Thx for the idea, but it is 90.
"Tibor Karaszi" wrote:
> Below code (taken from Books Online) gives me a "proper" error message:
> OPEN SYMMETRIC KEY SymKeyMarketing3
> DECRYPTION BY CERTIFICATE MarketingCert9;
> Server: Msg 15151, Level 16, State 1, Line 1
> Cannot find the symmetric key 'SymKeyMarketing3', because it does not exist or you do not have
> permission.
> Perhaps the compatibility level for your database is lower than 90?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
> >I am running through a great 2005 Encryption example at the following link:
> > http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx
> > I keep running into one problem though. When I execute the following at
> > this link
> > "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> > I get the following error
> > "Msg 156, Level 15, State 1, Line 2
> > Incorrect syntax near the keyword 'BY'."
> > Anybody have any idea what is going on?
> >
> >
> >
>|||Are you running the release version of SQL Server 2005? This syntax was
different in some of the CTP versions.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>I am running through a great 2005 Encryption example at the following link:
> http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx
> I keep running into one problem though. When I execute the following at
> this link
> "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> I get the following error
> "Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'BY'."
> Anybody have any idea what is going on?
>
>|||No. I had just thought of that and noticed that I was on Beta 2. We've got
an MSDN subscription, so I'll get the latest and greatest. Thx.
"Roger Wolter[MSFT]" wrote:
> Are you running the release version of SQL Server 2005? This syntax was
> different in some of the CTP versions.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
> >I am running through a great 2005 Encryption example at the following link:
> > http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx
> > I keep running into one problem though. When I execute the following at
> > this link
> > "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> > I get the following error
> > "Msg 156, Level 15, State 1, Line 2
> > Incorrect syntax near the keyword 'BY'."
> > Anybody have any idea what is going on?
> >
> >
> >
>
>

Encryption Example

I am running through a great 2005 Encryption example at the following link:
http://blogs.msdn.com/lcris/archive.../16/504692.aspx
I keep running into one problem though. When I execute the following at
this link
"open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
I get the following error
"Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'BY'."
Anybody have any idea what is going on?Below code (taken from Books Online) gives me a "proper" error message:
OPEN SYMMETRIC KEY SymKeyMarketing3
DECRYPTION BY CERTIFICATE MarketingCert9;
Server: Msg 15151, Level 16, State 1, Line 1
Cannot find the symmetric key 'SymKeyMarketing3', because it does not exist
or you do not have
permission.
Perhaps the compatibility level for your database is lower than 90?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>I am running through a great 2005 Encryption example at the following link:
> http://blogs.msdn.com/lcris/archive.../16/504692.aspx
> I keep running into one problem though. When I execute the following at
> this link
> "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> I get the following error
> "Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'BY'."
> Anybody have any idea what is going on?
>
>|||Thx for the idea, but it is 90.
"Tibor Karaszi" wrote:

> Below code (taken from Books Online) gives me a "proper" error message:
> OPEN SYMMETRIC KEY SymKeyMarketing3
> DECRYPTION BY CERTIFICATE MarketingCert9;
> Server: Msg 15151, Level 16, State 1, Line 1
> Cannot find the symmetric key 'SymKeyMarketing3', because it does not exis
t or you do not have
> permission.
> Perhaps the compatibility level for your database is lower than 90?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>|||Are you running the release version of SQL Server 2005? This syntax was
different in some of the CTP versions.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>I am running through a great 2005 Encryption example at the following link:
> http://blogs.msdn.com/lcris/archive.../16/504692.aspx
> I keep running into one problem though. When I execute the following at
> this link
> "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> I get the following error
> "Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'BY'."
> Anybody have any idea what is going on?
>
>|||No. I had just thought of that and noticed that I was on Beta 2. We've got
an MSDN subscription, so I'll get the latest and greatest. Thx.
"Roger Wolter[MSFT]" wrote:

> Are you running the release version of SQL Server 2005? This syntax was
> different in some of the CTP versions.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>
>|||Below code (taken from Books Online) gives me a "proper" error message:
OPEN SYMMETRIC KEY SymKeyMarketing3
DECRYPTION BY CERTIFICATE MarketingCert9;
Server: Msg 15151, Level 16, State 1, Line 1
Cannot find the symmetric key 'SymKeyMarketing3', because it does not exist
or you do not have
permission.
Perhaps the compatibility level for your database is lower than 90?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>I am running through a great 2005 Encryption example at the following link:
> http://blogs.msdn.com/lcris/archive.../16/504692.aspx
> I keep running into one problem though. When I execute the following at
> this link
> "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> I get the following error
> "Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'BY'."
> Anybody have any idea what is going on?
>
>|||Thx for the idea, but it is 90.
"Tibor Karaszi" wrote:

> Below code (taken from Books Online) gives me a "proper" error message:
> OPEN SYMMETRIC KEY SymKeyMarketing3
> DECRYPTION BY CERTIFICATE MarketingCert9;
> Server: Msg 15151, Level 16, State 1, Line 1
> Cannot find the symmetric key 'SymKeyMarketing3', because it does not exis
t or you do not have
> permission.
> Perhaps the compatibility level for your database is lower than 90?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>|||Are you running the release version of SQL Server 2005? This syntax was
different in some of the CTP versions.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>I am running through a great 2005 Encryption example at the following link:
> http://blogs.msdn.com/lcris/archive.../16/504692.aspx
> I keep running into one problem though. When I execute the following at
> this link
> "open symmetric key Doc2Key DECRYPTION BY certificate Doc2cert"
> I get the following error
> "Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'BY'."
> Anybody have any idea what is going on?
>
>|||No. I had just thought of that and noticed that I was on Beta 2. We've got
an MSDN subscription, so I'll get the latest and greatest. Thx.
"Roger Wolter[MSFT]" wrote:

> Are you running the release version of SQL Server 2005? This syntax was
> different in some of the CTP versions.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "CLM" <CLM@.discussions.microsoft.com> wrote in message
> news:382B3A15-06A0-4C0B-A962-047D67FC8EDA@.microsoft.com...
>
>sql

Thursday, March 22, 2012

Encryption at the database field level

Hello Everyone

I need a solution for the following problem ASAP

Configuration
SQL 2000 ENT Edition
Client - Server Application Designed in VB

I have one filed in one of my tables in a database E.g Credit Card Number. I need this fields to be Encrypted so that no body can see and mis use that information. One option is to change the application and incorporate the encryption logic into the application. Does SQL 2000 Provides some kind of Encryption at a field level? or can i manage this thing at the database level? I desperatelly want to do it at the database level.Refer to this MSDN link (http://msdn.microsoft.com/library/en-us/dnnetsec/html/SecNetHT19.asp) about using SSL communication.

Also on SQL Server level you can use MULTI-PROTOCOL netlib with ENCRYPTION option for the security, refer to books online for more information.

Encrypting with certificate and symmetric key

I posted the following question in the programming section on 3/19 and did
not get any responses. Can anyone here help me out?
I can avoid opening a symmetric key when I decrypt data by using the new
function "decryptbykeyautocert."
But there does not seem to be anything compareable for encrypting.
So I guess that in each of my data mod procs (INSERT/UPDATE) that performs
an encryption I will need to 1. open the key, 2. perform the mod, and then 3.
close the key.
Is this correct? Why isn't there a comparable function for encrypt? What
is the danger of inadvertantly leaving the key open? Will it close on
rollback?
Listed below is some code that provides and example of the issue:
USE master
--DROP DATABASE test
CREATE DATABASE test
USE test
IF object_ID('CreditCards') IS NOT NULL
DROP TABLE creditCards
GO
create table CreditCards (
Id int IDENTITY,
ccno varchar(20),
ccnoe varbinary(2000)
)
GO
INSERT CreditCards (ccno) VALUES ('1234567890')
GO
SELECT * FROM creditcards
GO
--Keys
--create database master key
CREATE master key
ENCRYPTION BY password = 'TestKey(123)'
--create the certificates that protects the data encryption keys
CREATE certificate CCE_Cert
authorization dbo with subject = 'CCE_Cert '
-- View certificates in database
select * from sys.certificates
-- Create symmetric key
CREATE symmetric key CCE_Key
with algorithm = AES_256
ENCRYPTION BY certificate CCE_Cert
select * from sys.symmetric_keys
open symmetric key CCE_Key
decryption by certificate CCE_Cert
--Encryption
--encrypt dat with key
UPDATE creditcards
SET ccnoe=encryptByKey(Key_GUID('CCE_Key'), ccno)
SELECT * FROM creditcards
--confirm key is open
select * from sys.openkeys
--view as raw data
SELECT * FROM creditcards
--Idccnoccnoe
--112345678900x00506334876D334AB3EA195508AC73E601000000BE92F21E 05800531482DE328AB76E15576D029C289F09F577F09BDEA1F 2A027C
--view as decrypted
SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
FROM creditcards
--ccnoccnoe
--12345678901234567890
close all symmetric keys
--view after key is closed
SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
FROM creditcards
--ccnoccnoe
--1234567890NULL
--use decryptbykeyautocert to avoid opening key
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
ccnoe)) as ccnoe
FROM creditcards
--now encrypt
INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
--but this does not work, it is encrypted as NULL
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
ccnoe)) as ccnoe
FROM creditcards
--open the key then INSERT
open symmetric key CCE_Key
decryption by certificate CCE_Cert
INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
ccnoe)) as ccnoe
FROM creditcards
--now it works
--but there is no encryptbykeyautocert, only decrypt
Hi Dave
See inline:
"Dave" wrote:

> I posted the following question in the programming section on 3/19 and did
> not get any responses. Can anyone here help me out?
> --
> I can avoid opening a symmetric key when I decrypt data by using the new
> function "decryptbykeyautocert."
> But there does not seem to be anything compareable for encrypting.
> So I guess that in each of my data mod procs (INSERT/UPDATE) that performs
> an encryption I will need to 1. open the key, 2. perform the mod, and then 3.
> close the key.
> Is this correct? Why isn't there a comparable function for encrypt? What
> is the danger of inadvertantly leaving the key open? Will it close on
> rollback?
> Listed below is some code that provides and example of the issue:
> USE master
> --DROP DATABASE test
> CREATE DATABASE test
> USE test
> IF object_ID('CreditCards') IS NOT NULL
> DROP TABLE creditCards
> GO
> create table CreditCards (
> Id int IDENTITY,
> ccno varchar(20),
> ccnoe varbinary(2000)
> )
> GO
> INSERT CreditCards (ccno) VALUES ('1234567890')
> GO
> SELECT * FROM creditcards
> GO
> --
> --Keys
> --create database master key
> CREATE master key
> ENCRYPTION BY password = 'TestKey(123)'
> --create the certificates that protects the data encryption keys
> CREATE certificate CCE_Cert
> authorization dbo with subject = 'CCE_Cert '
> -- View certificates in database
> select * from sys.certificates
> -- Create symmetric key
> CREATE symmetric key CCE_Key
> with algorithm = AES_256
> ENCRYPTION BY certificate CCE_Cert
> select * from sys.symmetric_keys
> open symmetric key CCE_Key
> decryption by certificate CCE_Cert
> --
> --Encryption
> --encrypt dat with key
> UPDATE creditcards
> SET ccnoe=encryptByKey(Key_GUID('CCE_Key'), ccno)
> SELECT * FROM creditcards
> --confirm key is open
> select * from sys.openkeys
> --view as raw data
> SELECT * FROM creditcards
> --Idccnoccnoe
> --112345678900x00506334876D334AB3EA195508AC73E601000000BE92F21E 05800531482DE328AB76E15576D029C289F09F577F09BDEA1F 2A027C
> --view as decrypted
> SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
> FROM creditcards
> --ccnoccnoe
> --12345678901234567890
> close all symmetric keys
> --view after key is closed
> SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
> FROM creditcards
> --ccnoccnoe
> --1234567890NULL
>
> --use decryptbykeyautocert to avoid opening key
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --now encrypt
> INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
> encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
>
If you did a select * from CreditCards you would get
Id ccno ccnoe
-- --
------1
1234567890
0x00599BE28153D949881C25E2DFCDCB3A0100000018FAD0F3 E302CA5591F1F4B9AEE8CAE969D538149C1C774DF278DE987A 7990EDC50917BB2E98106C4D8C357C1C2D2FD2
2 1234567890 NULL
(2 row(s) affected)
i.e. the underlying value is NULL and the encryption has not worked.
Therefore decrypting a NULL value does not make sense!

> --but this does not work, it is encrypted as NULL
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --open the key then INSERT
> open symmetric key CCE_Key
> decryption by certificate CCE_Cert
> INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
> encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --now it works
That is because the key has been re-opened!

> --but there is no encryptbykeyautocert, only decrypt
>
I am not sure why there is not one, but I can see that if you have a one
key/one certificate mapping then this may be something hat would be useful,
but if you have (say) 10 keys encrypted by the one certificate do you encrypt
with all the keys, the first key or one key at random? The first option would
be very expensive, the second option would not have great value and would
potentially be very dangerous if it was used without understanding what was
happening, and the third option depends on the meaning and implementation of
random! I would probably not advise the use of decryptbykeyautocert either if
you want quicker decryptions!
You may want to put in a request at
https://connect.microsoft.com/SQLServer/Feedback
John
sql

Encrypting with certificate and symmetric key

I posted the following question in the programming section on 3/19 and did
not get any responses. Can anyone here help me out?
--
I can avoid opening a symmetric key when I decrypt data by using the new
function "decryptbykeyautocert."
But there does not seem to be anything compareable for encrypting.
So I guess that in each of my data mod procs (INSERT/UPDATE) that performs
an encryption I will need to 1. open the key, 2. perform the mod, and then 3
.
close the key.
Is this correct? Why isn't there a comparable function for encrypt? What
is the danger of inadvertantly leaving the key open? Will it close on
rollback?
Listed below is some code that provides and example of the issue:
USE master
--DROP DATABASE test
CREATE DATABASE test
USE test
IF object_ID('CreditCards') IS NOT NULL
DROP TABLE creditCards
GO
create table CreditCards (
Id int IDENTITY,
ccno varchar(20),
ccnoe varbinary(2000)
)
GO
INSERT CreditCards (ccno) VALUES ('1234567890')
GO
SELECT * FROM creditcards
GO
--Keys
--create database master key
CREATE master key
ENCRYPTION BY password = 'TestKey(123)'
--create the certificates that protects the data encryption keys
CREATE certificate CCE_Cert
authorization dbo with subject = 'CCE_Cert '
-- View certificates in database
select * from sys.certificates
-- Create symmetric key
CREATE symmetric key CCE_Key
with algorithm = AES_256
ENCRYPTION BY certificate CCE_Cert
select * from sys.symmetric_keys
open symmetric key CCE_Key
decryption by certificate CCE_Cert
--Encryption
--encrypt dat with key
UPDATE creditcards
SET ccnoe=encryptByKey(Key_GUID('CCE_Key'), ccno)
SELECT * FROM creditcards
--confirm key is open
select * from sys.openkeys
--view as raw data
SELECT * FROM creditcards
--Id ccno ccnoe
-- 1 1234567890 0x00506334876D334AB3EA19550
8AC73E601000000BE92F21E05800531482
DE328AB76E15576D029C289F09F577F09BDEA1F2
A027C
--view as decrypted
SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
FROM creditcards
--ccno ccnoe
--1234567890 1234567890
close all symmetric keys
--view after key is closed
SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
FROM creditcards
--ccno ccnoe
--1234567890 NULL
--use decryptbykeyautocert to avoid opening key
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert')
, NULL,
ccnoe)) as ccnoe
FROM creditcards
--now encrypt
INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
encryptByKey(Key_GUID('CCE_Key'),'123456
7890'))
--but this does not work, it is encrypted as NULL
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert')
, NULL,
ccnoe)) as ccnoe
FROM creditcards
--open the key then INSERT
open symmetric key CCE_Key
decryption by certificate CCE_Cert
INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
encryptByKey(Key_GUID('CCE_Key'),'123456
7890'))
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert')
, NULL,
ccnoe)) as ccnoe
FROM creditcards
--now it works
--but there is no encryptbykeyautocert, only decryptHi Dave
See inline:
"Dave" wrote:

> I posted the following question in the programming section on 3/19 and did
> not get any responses. Can anyone here help me out?
> --
> I can avoid opening a symmetric key when I decrypt data by using the new
> function "decryptbykeyautocert."
> But there does not seem to be anything compareable for encrypting.
> So I guess that in each of my data mod procs (INSERT/UPDATE) that performs
> an encryption I will need to 1. open the key, 2. perform the mod, and then
3.
> close the key.
> Is this correct? Why isn't there a comparable function for encrypt? What
> is the danger of inadvertantly leaving the key open? Will it close on
> rollback?
> Listed below is some code that provides and example of the issue:
> USE master
> --DROP DATABASE test
> CREATE DATABASE test
> USE test
> IF object_ID('CreditCards') IS NOT NULL
> DROP TABLE creditCards
> GO
> create table CreditCards (
> Id int IDENTITY,
> ccno varchar(20),
> ccnoe varbinary(2000)
> )
> GO
> INSERT CreditCards (ccno) VALUES ('1234567890')
> GO
> SELECT * FROM creditcards
> GO
> --
> --Keys
> --create database master key
> CREATE master key
> ENCRYPTION BY password = 'TestKey(123)'
> --create the certificates that protects the data encryption keys
> CREATE certificate CCE_Cert
> authorization dbo with subject = 'CCE_Cert '
> -- View certificates in database
> select * from sys.certificates
> -- Create symmetric key
> CREATE symmetric key CCE_Key
> with algorithm = AES_256
> ENCRYPTION BY certificate CCE_Cert
> select * from sys.symmetric_keys
> open symmetric key CCE_Key
> decryption by certificate CCE_Cert
> --
> --Encryption
> --encrypt dat with key
> UPDATE creditcards
> SET ccnoe=encryptByKey(Key_GUID('CCE_Key'), ccno)
> SELECT * FROM creditcards
> --confirm key is open
> select * from sys.openkeys
> --view as raw data
> SELECT * FROM creditcards
> --Id ccno ccnoe
> -- 1 1234567890 0x00506334876D334AB3EA19550
8AC73E601000000BE92F21E058005314
82DE328AB76E15576D029C289F09F577F09BDEA1
F2A027C
> --view as decrypted
> SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
> FROM creditcards
> --ccno ccnoe
> --1234567890 1234567890
> close all symmetric keys
> --view after key is closed
> SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
> FROM creditcards
> --ccno ccnoe
> --1234567890 NULL
>
> --use decryptbykeyautocert to avoid opening key
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert')
, NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --now encrypt
> INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
> encryptByKey(Key_GUID('CCE_Key'),'123456
7890'))
>
If you did a select * from CreditCards you would get
Id ccno ccnoe
-- --
----
----1
1234567890
0x00599BE28153D949881C25E2DFCDCB3A010000
0018FAD0F3E302CA5591F1F4B9AEE8CAE969
D538149C1C774DF278DE987A7990EDC50917BB2E
98106C4D8C357C1C2D2FD2
2 1234567890 NULL
(2 row(s) affected)
i.e. the underlying value is NULL and the encryption has not worked.
Therefore decrypting a NULL value does not make sense!

> --but this does not work, it is encrypted as NULL
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert')
, NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --open the key then INSERT
> open symmetric key CCE_Key
> decryption by certificate CCE_Cert
> INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
> encryptByKey(Key_GUID('CCE_Key'),'123456
7890'))
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert')
, NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --now it works
That is because the key has been re-opened!

> --but there is no encryptbykeyautocert, only decrypt
>
I am not sure why there is not one, but I can see that if you have a one
key/one certificate mapping then this may be something hat would be useful,
but if you have (say) 10 keys encrypted by the one certificate do you encryp
t
with all the keys, the first key or one key at random? The first option woul
d
be very expensive, the second option would not have great value and would
potentially be very dangerous if it was used without understanding what was
happening, and the third option depends on the meaning and implementation of
random! I would probably not advise the use of decryptbykeyautocert either i
f
you want quicker decryptions!
You may want to put in a request at
https://connect.microsoft.com/SQLServer/Feedback
John

Encrypting with certificate and symmetric key

I posted the following question in the programming section on 3/19 and did
not get any responses. Can anyone here help me out?
--
I can avoid opening a symmetric key when I decrypt data by using the new
function "decryptbykeyautocert."
But there does not seem to be anything compareable for encrypting.
So I guess that in each of my data mod procs (INSERT/UPDATE) that performs
an encryption I will need to 1. open the key, 2. perform the mod, and then 3.
close the key.
Is this correct? Why isn't there a comparable function for encrypt? What
is the danger of inadvertantly leaving the key open? Will it close on
rollback?
Listed below is some code that provides and example of the issue:
USE master
--DROP DATABASE test
CREATE DATABASE test
USE test
IF object_ID('CreditCards') IS NOT NULL
DROP TABLE creditCards
GO
create table CreditCards (
Id int IDENTITY,
ccno varchar(20),
ccnoe varbinary(2000)
)
GO
INSERT CreditCards (ccno) VALUES ('1234567890')
GO
SELECT * FROM creditcards
GO
--
--Keys
--create database master key
CREATE master key
ENCRYPTION BY password = 'TestKey(123)'
--create the certificates that protects the data encryption keys
CREATE certificate CCE_Cert
authorization dbo with subject = 'CCE_Cert '
-- View certificates in database
select * from sys.certificates
-- Create symmetric key
CREATE symmetric key CCE_Key
with algorithm = AES_256
ENCRYPTION BY certificate CCE_Cert
select * from sys.symmetric_keys
open symmetric key CCE_Key
decryption by certificate CCE_Cert
--
--Encryption
--encrypt dat with key
UPDATE creditcards
SET ccnoe=encryptByKey(Key_GUID('CCE_Key'), ccno)
SELECT * FROM creditcards
--confirm key is open
select * from sys.openkeys
--view as raw data
SELECT * FROM creditcards
--Id ccno ccno
--1 1234567890 0x00506334876D334AB3EA195508AC73E601000000BE92F21E05800531482DE328AB76E15576D029C289F09F577F09BDEA1F2A027C
--view as decrypted
SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
FROM creditcards
--ccno ccnoe
--1234567890 1234567890
close all symmetric keys
--view after key is closed
SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
FROM creditcards
--ccno ccnoe
--1234567890 NULL
--use decryptbykeyautocert to avoid opening key
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
ccnoe)) as ccnoe
FROM creditcards
--now encrypt
INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
--but this does not work, it is encrypted as NULL
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
ccnoe)) as ccnoe
FROM creditcards
--open the key then INSERT
open symmetric key CCE_Key
decryption by certificate CCE_Cert
INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
ccnoe)) as ccnoe
FROM creditcards
--now it works
--but there is no encryptbykeyautocert, only decryptHi Dave
See inline:
"Dave" wrote:
> I posted the following question in the programming section on 3/19 and did
> not get any responses. Can anyone here help me out?
> --
> I can avoid opening a symmetric key when I decrypt data by using the new
> function "decryptbykeyautocert."
> But there does not seem to be anything compareable for encrypting.
> So I guess that in each of my data mod procs (INSERT/UPDATE) that performs
> an encryption I will need to 1. open the key, 2. perform the mod, and then 3.
> close the key.
> Is this correct? Why isn't there a comparable function for encrypt? What
> is the danger of inadvertantly leaving the key open? Will it close on
> rollback?
> Listed below is some code that provides and example of the issue:
> USE master
> --DROP DATABASE test
> CREATE DATABASE test
> USE test
> IF object_ID('CreditCards') IS NOT NULL
> DROP TABLE creditCards
> GO
> create table CreditCards (
> Id int IDENTITY,
> ccno varchar(20),
> ccnoe varbinary(2000)
> )
> GO
> INSERT CreditCards (ccno) VALUES ('1234567890')
> GO
> SELECT * FROM creditcards
> GO
> --
> --Keys
> --create database master key
> CREATE master key
> ENCRYPTION BY password = 'TestKey(123)'
> --create the certificates that protects the data encryption keys
> CREATE certificate CCE_Cert
> authorization dbo with subject = 'CCE_Cert '
> -- View certificates in database
> select * from sys.certificates
> -- Create symmetric key
> CREATE symmetric key CCE_Key
> with algorithm = AES_256
> ENCRYPTION BY certificate CCE_Cert
> select * from sys.symmetric_keys
> open symmetric key CCE_Key
> decryption by certificate CCE_Cert
> --
> --Encryption
> --encrypt dat with key
> UPDATE creditcards
> SET ccnoe=encryptByKey(Key_GUID('CCE_Key'), ccno)
> SELECT * FROM creditcards
> --confirm key is open
> select * from sys.openkeys
> --view as raw data
> SELECT * FROM creditcards
> --Id ccno ccnoe
> --1 1234567890 0x00506334876D334AB3EA195508AC73E601000000BE92F21E05800531482DE328AB76E15576D029C289F09F577F09BDEA1F2A027C
> --view as decrypted
> SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
> FROM creditcards
> --ccno ccnoe
> --1234567890 1234567890
> close all symmetric keys
> --view after key is closed
> SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
> FROM creditcards
> --ccno ccnoe
> --1234567890 NULL
>
> --use decryptbykeyautocert to avoid opening key
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --now encrypt
> INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
> encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
>
If you did a select * from CreditCards you would get
Id ccno ccnoe
-- --
------1
1234567890
0x00599BE28153D949881C25E2DFCDCB3A0100000018FAD0F3E302CA5591F1F4B9AEE8CAE969D538149C1C774DF278DE987A7990EDC50917BB2E98106C4D8C357C1C2D2FD2
2 1234567890 NULL
(2 row(s) affected)
i.e. the underlying value is NULL and the encryption has not worked.
Therefore decrypting a NULL value does not make sense!
> --but this does not work, it is encrypted as NULL
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --open the key then INSERT
> open symmetric key CCE_Key
> decryption by certificate CCE_Cert
> INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
> encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --now it works
That is because the key has been re-opened!
> --but there is no encryptbykeyautocert, only decrypt
>
I am not sure why there is not one, but I can see that if you have a one
key/one certificate mapping then this may be something hat would be useful,
but if you have (say) 10 keys encrypted by the one certificate do you encrypt
with all the keys, the first key or one key at random? The first option would
be very expensive, the second option would not have great value and would
potentially be very dangerous if it was used without understanding what was
happening, and the third option depends on the meaning and implementation of
random! I would probably not advise the use of decryptbykeyautocert either if
you want quicker decryptions!
You may want to put in a request at
https://connect.microsoft.com/SQLServer/Feedback
John

Wednesday, March 21, 2012

Encrypting the configuration file values stored in SQL server

Hi All,

I have the following requirement. I need to store the password for the connection manager in the configuration file. The sink for the configuration file is SQL Server. Though the password field appears as "******" the actual value is being taken as ""******" itself. If i update the SQL server table with the correct value, then the package starts working. But, the password is shown as clear text.

If i write logic to encrypt the password column in the configuration table, is there a way to tell the SSIS execute engine to decrypt the password before using the same for making the connection.

Is there a place holder, where i can write the decrypt code so that the decrypted password can be sent to the execution engine?

Thanks In Advance,

Madhu

I think the short answer to this is no, and no code hooks either.

I think though that there is also an argument, that says it would not be more secure than what you have now. If you encrypt the data, you need to then secure the key. So what will you do to secure the key? Why not use strong security to secure the password data instead of worrying about how to secure the key? I accept that the encryption adds an extra step, but I'm not convinced it will actually be any safer.

|||I'm not sure if it's a good idea, but couldn't he create a script task to decrypt the password and reset the connection manager's connectionstring property before the connection manager is used in the package?|||

Yes and no. Some connections are used before your script task could run, such as connections used for logging.

How would you secure the key used to decrypt the password? You need to secure the encryption/decryption key, so why not just secure the password to start with?

|||DarrenSQLIS is right the recommended way to do this is to store the password in the connection. SSIS will automatically encrypt these so that they are not stored in cleartext.|||Thanks for the thoughts Darren. As suggested by you, way to go is to store the password in SQL server and make sure that the access to the configuration table is only for administrators.|||

Denise, I think you are talking about the package level encryption, protection levels and such like. Nice though it is, it is not very useful, as I think you should "externalise" any kind of security information.

Using package encryption becomes unfeasible when you have to migrate packages between environments. Configurations solve that migration issue, but don't give you the encryption that is often seen as a requirement for some organisations. I'd argue that is should not be a big deal, secure the password so you don't have to worry about the key, but often it is an internal "standard" that must be complied with.

Still we have the choice of package encryption, which is better than not!

Sunday, March 11, 2012

Encrypted Database transfer problem

Hi,

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

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

To decrypt data i use DecryptByKeyAutoCert.

On my server this encryption & decryption is working perfectly.

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

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

Thank you.

Pls give me soln. i am worried abt it.

Gaurav

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

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

Thanks
Laurentiu

|||

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

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

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

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

Thanks

Gaurav

|||

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

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

Thanks
Laurentiu

'EncryptByPassPhrase' is not a recognized function name.

I am trying to run the following code in SQL Server 2005:

DECLARE @.cleartext NVARCHAR(100)

DECLARE @.encryptedstuff NVARCHAR(100)

DECLARE @.decryptedstuff NVARCHAR(100)

SET @.cleartext = 'XYZ'

SET @.encryptedstuff = EncryptByPassPhrase('12345', @.cleartext)

SELECT @.encryptedstuff

SET @.decryptedstuff = DecryptByPassphrase('12345', @.encryptedstuff)

SELECT @.decryptedstuff

and am recieving an error:

Msg 195, Level 15, State 10, Line 5

'EncryptByPassPhrase' is not a recognized function name.

Msg 195, Level 15, State 10, Line 7

'DecryptByPassphrase' is not a recognized function name.

It appears as though this EncryptByPassPhrase and DecryptByPassphrase as supported in 2005 T-SQL commands but when I execute this code in SQL Server Studio it errors out.

Anyone know why?

What's the compatibility level of your database?

encrypt(string) Question!

SQL Server 2000:

################################################## ######
I run the following as a normal query from Analyzer:
################################################## ######

SELECT encrypt(user_password) FROM emp WHERE user_id = 1

################################################## #######
I run the following query from inside a stored proc:
################################################## #######

SELECT encrypt(user_password) FROM emp WHERE user_id = 1

################################################## #######
Question??
################################################## #######

If the data inside the emp table does not change, how can these two
queries return different values?

Any help would be much appreciated!

thanks,
Russ> SELECT encrypt(user_password) FROM emp WHERE user_id = 1
> SELECT encrypt(user_password) FROM emp WHERE user_id = 1

> If the data inside the emp table does not change, how can these two
> queries return different values?

They return different values because the encrypt function 'salts' the data
to prevent someone from just encrypting a bunch of stuff to figure out the
other data in the table.

The Unix crypt function used to do this by putting two random characters on
the front of the data string and also on the front of the encryption string
using the 'salt' as part of the key.

Regards,
Jim|||In addition to James's reply, note that the Encrypt function is undocumented
so its behaviour can change between versions of the product. Don't rely on
it in production code. Generate a password hash client-side would be my
suggestion.

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:0eadncyJC6oF1hzcRVn-tg@.giganews.com...
> In addition to James's reply, note that the Encrypt function is
undocumented
> so its behaviour can change between versions of the product. Don't rely on
> it in production code. Generate a password hash client-side would be my
> suggestion.

And in the at least one case I looked at, trivial to decrypt.

> --
> David Portas
> SQL Server MVP
> --

Wednesday, March 7, 2012

Encoding problem

Hi,

I'm struggling to understand why I'm getting the following error when I call "sp_xml_preparedocument"

"XML parsing error: Switch from current encoding to specified encoding not supported."

The header of the xml document contains "<?xml version="1.0" encoding="UTF-8"?>"

XML data is passed into my stored procedure via an "ntext" variable so I would assume that there would'nt be an encoding issue.

Does anyone have any pointers for me? BTW I'm using SQL Server 2000.

Many thanks in advance.

Ian.

PS I've just changed the parameter from "ntext" to "text" and it works fine. Don't understand even more now.

I think ntext uses UTF-16 (respectively the older UCS-2) not UTF-8.

Encoding errors

Somewhat complex:
I'm getting the following error in a sp (SQL Server 2000):
"Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line
8
XML parsing error: Switch from current encoding to specified encoding not
supported."
When a ntext xml string has the encoding set to "utf-8", but not utf-16. So
it would seem
obvious that the document stored in the database is in utf-16...?
However, when the data is written to the database, the XmlDocument class in
..NET (1.1)
is convinced the document is utf-8 (looking in the VS debugger). The call to
the sp in
C# uses:
parameter = command.Parameters.Add("@.Body", SqlDbType.NText);
parameter.Direction = ParameterDirection.Input;
parameter.Value = xd.OuterXml;
where xd is the XmlDocument. I can only conclude that somewhere the xml is
being converted
into a .NET string (and hence utf-16), I would guess at .OuterXml. But I
can't see a way
round this. At some point I need to pass the parameter value!
Any help would be appreciated.
Rgds
Peter Johnston
The problem here is that both UTF-16 and UTF-8 are encodings for UCS-4
Unicode characters but they are quite different beasts at the byte level.
SQL Server's ntext type is for historical reasons supporting UCS-2 Unicode
and for our XML parser thus should only be used to contain UCS-2 or UTF-16
encoded XML (UTF-16 uses two 2-byte code points, the so called surrogate
pairs, to represent the Unicode characters that go beyond UCS-2).
UTF-8 is a variable-length multi-byte encoding (1 to 4 bytes) of UCS-4 and
thus conflicts with the two-byte representation of ntext. The server-side
XML parser does not allow switching to UTF-8 while it parses ntext
characters since it could lead to data corruption.
Unfortunately, SQL Server does not really support a UFT-8 code page for
text/varchar either. I think that the windows-1256 encoding is mostly UTF-8
code point safe though (meaning, it does not change the code points to ?).
If you need to load data in SQL Server 2000 that contains UTF-8 encoded
data, you have the following options:
1. Change the encoding of the document to UTF-16 before passing it to the
server as ntext parameter (both the native and managed client XML libraries
have mechanisms to set the encoding)
2. If you know that you can just drop the encoding indicator, since you are
only loading ASCII anyway, do so. But be careful - even some 127-255 range
characters can get corrupted this way.
3. Load the data with the UTF-8 encoding into a text that has a collation
that implies Windows-1256.
In SQL Server 2005, we have added the ability to load the data into the XML
datatype either as XML or as varbinary() in which cases UTF-8 will work
without problems.
Best regards
Michael
"Peter Johnston" <nospam@.nospam.com> wrote in message
news:usP7DnCwFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Somewhat complex:
> I'm getting the following error in a sp (SQL Server 2000):
> "Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument,
> Line 8
> XML parsing error: Switch from current encoding to specified encoding not
> supported."
> When a ntext xml string has the encoding set to "utf-8", but not utf-16.
> So it would seem
> obvious that the document stored in the database is in utf-16...?
> However, when the data is written to the database, the XmlDocument class
> in .NET (1.1)
> is convinced the document is utf-8 (looking in the VS debugger). The call
> to the sp in
> C# uses:
> parameter = command.Parameters.Add("@.Body", SqlDbType.NText);
> parameter.Direction = ParameterDirection.Input;
> parameter.Value = xd.OuterXml;
> where xd is the XmlDocument. I can only conclude that somewhere the xml is
> being converted
> into a .NET string (and hence utf-16), I would guess at .OuterXml. But I
> can't see a way
> round this. At some point I need to pass the parameter value!
> Any help would be appreciated.
> Rgds
> Peter Johnston
>

Monday, March 26, 2012

Encryption not supported on SQL Server - Error Message

Encryption not supported on SQL Server - Error Message

Encryption not supported on SQL Server - Error Message

Encryption not supported on SQL Server - Error Message

Encryption not supported on SQL Server - Error Message

Encryption Not Supported Error

Encryption Not Supported Error

Encryption Key

Encryption Example

Encryption Example

Thursday, March 22, 2012

Encryption at the database field level

Encrypting with certificate and symmetric key

Encrypting with certificate and symmetric key

Encrypting with certificate and symmetric key

Wednesday, March 21, 2012

Encrypting the configuration file values stored in SQL server

Sunday, March 11, 2012

Encrypted Database transfer problem

'EncryptByPassPhrase' is not a recognized function name.

encrypt(string) Question!

Wednesday, March 7, 2012

Encoding problem

Encoding errors

Empty Column

Blog Archive

About Me