Change database attributes with SQL?

Is there a way to change the password or encryption settings with SQL or do I need to use Compact from code to do so?

I'm trying to work around the issue that USE does not accept a password.

if you have an existing SQL CE 2 or SQL Mobile database and want to change the password or encryption settings, the only option is to run the Compact method on the SqlCeEngine object. If you have forgotten the old password, you are out of luck - there is no way to reset it.

Darren

|||I was afraid of that. Again, we need a bit of help in the SQL Ev SQL. The USE statement needs to accept a Password argument for this engine.sql

Certificate Restore Not working.

I have been trying to use Encryption to encrypt a few key fields. So my first goal was to create all the items I would need to effectivly use encryption, to encrypt a few key fields.

First I created a sample sql script to create a certificate, create a table and insert some test data encrypted. And then made sure I could decrypt the data encrypted with the certificate.

Second step was to test the backing up of the certificate, droping the certificate to similate a restore of the database. And then a restore of the certificate, and then see if the it was still possible to decrypt the existing encrypted data with the restored certificate.

I receive no errors when restoreing the certificate, but it does not properly decrypt the exisiting data.

Can anyone help and point out my mistake?

--Start Sql--

--Create Sample Cert CREATE CERTIFICATE SampleCert1 ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C' WITH SUBJECT = 'Sample Cert for Demo', EXPIRY_DATE = '04/18/2017'; --create Sample table with encrypted version CREATE TABLE Table_1 (Id int NOT NULL IDENTITY (1, 1), SSN_Encrypted varbinary(300) NOT NULL) ON [PRIMARY] GO --insert row with encrypted version. Insert into Table_1 (SSN_Encrypted) values (EncryptByCert(Cert_ID('SampleCert1'),'000-00-0000')) --returns '000-00-0000' for 'SampleCert1' since it was able to decrypt select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1' from Table_1 GO --backup Certificate BACKUP CERTIFICATE SampleCert1 TO FILE = 'D:\Backups\SampleCert.cer' WITH PRIVATE KEY ( FILE = 'D:\Backups\SampleCert.pvk' , DECRYPTION BY PASSWORD = N'728AC41753642403251BF8E7233EC0C' , ENCRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh' ); GO DROP CERTIFICATE SampleCert1; GO --Restore from backup. to simulate a restore from tape CREATE CERTIFICATE SampleCert1 FROM FILE = 'D:\Backups\SampleCert.cer' WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk', DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh'); --This should return the same ssn as was encrypted, if returns null the restore failed. select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1' from Table_1 --cleanup Removes Certificate from the system DROP CERTIFICATE SampleCert1; --removes the temporary table. Drop table Table_1

I think I found out why your statement is not working as you expected.

In the following query, the certificate is re-created from the backup, but only a decryption password (used to open the PVK file) is specified, but no encryption password is present. This will create the CERTIFICATE in SQL Server with the private key protected by the DB master key.

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh');

Instead of

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh',

ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C');

You can verify if my assumption is correct by running the following statement:

SELECT name, pvt_key_encryption_type_desc FROM sys.certificates

If I am right, you can just change the protection mechanism for the existing CERTIFICATE:

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C' );

Let us know if this information helped.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Well today is not my lucky day I am afraid. I can't verify yet.

Last night we rolled out a policy to enforce passwords complexity, and length requirements.

So I am geting an error just trying to run the same script, even though I am using NT Authentication, and have updated my password to conform to the new policy.

Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

Does Create Certificate use a different security creditial then the loged in Users?

I even tried using SA with the following set. with the same results.

Code Snippet

Alter Login [sa] with CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Any more idea's where I need to look to figure this out?

|||

Disabling the password policy checks is not available for cryptographic objects (certificates, keys, etc.), the only part of the policy enforced in such objects is the password complexity for newly created or modified objects/passphrases.

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '<<new password that complies with password policy>>' );

--returns '000-00-0000' for 'SampleCert1' since it was able to decrypt

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted, <<new password that complies with password policy>>') as varchar(12)) as 'SampleCert1'

from Table_1

Or if you prefer to use the DBMK to protect your certificate, you can change your query to the following:

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted) as varchar(12)) as 'SampleCert1'

from Table_1

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you for all your help.

Knowing what password I had to fix made it short work geting it working.

Owen.

Certificate not recognized by SQL 2005 - Encryption Problem

Hi,

We recently upgraded from SQL 2K to SQL 2005 on WINDOWS 2003 Server. The entire upgrade process has run pretty smoothly aside from encryption.

We have ASP.NET 1.x apps that connect to an internal server but fail when encrypted. We have tried all of the steps available on the MS KB and still have not resolved the issue.

On http://msdn2.microsoft.com/en-us/library/ms191192.aspx, we find the following:
To configure the server to accept encrypted connections

1. In SQL Server Configuration Manager, expand SQL Server 2005 Network Configuration, right-click Protocols for <server instance>, and then selectProperties.
2. In the Protocols for<instance name> Properties dialog box, on the Certificate tab, select the desired certificate from the drop down for the Certificate box, and then click OK.
3. On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box.
4. Restart the SQL Server service.

The problem is when we get to step 2, there are NO certificates available in the dropdown box.

This problem is solely the result of installing SQL Server 2005. The apps were successfully encrypting data connections prior to the install. All internal and non-encrypted connections work correctly and no other changes were made to the box aside from the SQL 2005 upgrade.

This issue has remained unresolved for almost two weeks now.... We have tried the KB articles many times over, none of which have made a difference. We have also created certificates many times over, checking Server Authentication in the EKU section and making sure the cert has a private key. The expiration date on the certs we create are in 2009 and are named using the FQDN. Clearly, I'm no certificate expert, but there has got to be something we're missing with the certificates since every other app connection works like it should

What can I do to get certificates to appear in the Certificate tab of the Properties dialog box? What else would you recommend?

Many thanks.

-Matt

Orcabbelle,

To help you troubleshoot this issue, I need you to share some more info,

(1) ERRORLOG of the SQL Server,

You should be able to find it under place similar to

'C:\Program Files\Microsoft SQL Server\MSSQL.XX\MSSQL\LOG\ERRORLOG''

Extract specific lines that show certificate if any.

(2) Run certutil.exe as

certutil.exe -v -store my.

pasted the output w.r.t the cert that you have just installed here.

Generally, there is also way to modify the registry value to force server encryption. But it is not recommended because it is errorprone. Let me know if you want to do that to get you problem solved faster.

|||Nan,

There is only one relevant line I can find in the errorlog:
2006-04-04 11:11:03.59 Server A self-generated certificate was successfully loaded for encryption.

Not sure what this means or where I can find the self-generated cert. Is it the cert in with the data files?

As for #2, we ran the cmd line you suggested and everything looks good aside from:
CERT_ARCHIVED_PROP_ID(19):
3988ce37fa66df4b79d75bb63c7f8d0d_c381bd65-7971-4be2-9c1b-aef143aed909
ERROR: Certificate public key does NOT match stored keyset

|||

So the certificate you are using is corrupted somehow.

One possible reason is that you have a previous installed certificate, and the newly installed certificate does not have permission to over write the private key of the old one. I am assuming you do have a certificates that have both public key and private key is expotable.

Try to clean up your certificate store using MMC. that have same FQDN as this new one has. Then install the certificate in the local store. Try to run as local admin to clean/install the certificate.

Let me know what you get.

Selfsigned certificate is newly added feature in SS2k5 that help to encrypt even user does not have certificate. the self signed certificate is stored in master database. It is briefly mentioned here. As result, it is possible to force encryption without provisioning a valid certificate in SS2k5

http://blogs.msdn.com/sql_protocols/archive/2005/10/04/476705.aspx

|||We cleaned up our MMC and our new cert seems to be fine (not corrupted). We want to use our cert, not the self-signed cert.

As soon as we upgraded, all of our ASP.NET apps that were using Encrypt=True in their connection strings simply stopped working. Nothing else on either box, the server or the client, was changed. We changed the connection string to Encrypt=False and the apps work like they should, aside from not being encrypted. What could cause this?

We have tried just about every MS KB article and recommendation we can find. We created new certificates using a CA server. We made sure they have had private keys and allowed server authentication. Force protocol encryption is set to YES on the server.

We have never been able to get the certificate we create to appear in the list. We are logging on the server as the domain admin and starting the service under the same account.

Any ideas why we can't see the cert in the list or why the encryption failed after a SQL upgrade?

Many thanks!
|||

First, Your sql service should run under admin account, otherwise it has no permissing to the private key and thus the cert would not show up in the drop down list of SSCM, visit KB article for more detail:
http://support.microsoft.com/?kbid=900495.

Secondly, SQL Server 2005 has a new kind cert called self-signed certificate, which means when server can not find a good cert to load in the cert store, it will load such a self-signed cert. the encryption failure you saw is due to the reason that your server might load a self-signed cert(To verify this, you can go to server errorlog and see there is keyword "self-signed certificate"), and you forced client encryption through setting "Encrypt=True". If you already forced server encryption, the connection should be encrypted. And if you require forcing client encryption, you need to have server loading a certificate issued by a trusted CA.

Thirdly, please run "certutil.exe -v -store my", the tool would verify whether the certs you installed are valid;and try change sql service running under LocalSystem account, see whether any cert appear in the list.

|||

The only thing that I can think of is that the certificates that you are generating and the one you used for SS2K do not meet the stricter requirements of SS2K5. Please have a look at this post to see what properties we require from a cert:
http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx.

Thanks,
Il-Sung.

|||II-Sung, thanks again for your help.

Ok, IT has had a look at what you said and has had trouble with #4 found on the link you provided.

4) The Certficate’s key usage must include AT_KEYEXCHANGE property, which means Key Encipherment (a0)

They were able to find the Key Encipherment value but ours is not (a0), it's (f0). Not sure if this makes a difference or not.

From running "certutil.exe -v -store my":

Key Usage

Digital Signature, Non-Repudiation, Key Encipherment, Data Encipherment (f0)

|||

Hi Orcabelle,

Would you be able to try using a certificate that has a key usage of 0xA0 instead of 0xF0 and see if that makes a difference?

Also, could you indicate whether you're generating your own self-signed certificate? If so, what is the command that you're using the generate it. I believe it should be similar to:

makecert -r -pe -n "CN= MySQLServerName" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

Thanks,
Il-Sung.

|||At this point we're happy to try anything....

Now my question is, how do I change key usage from F to A?
|||

Hi Orcabelle,

I did some more looking into this and a key usage of f0 is perfectly fine. This is very perplexing problem. Could you please confirm that you're looking at the correct entry of the "certutil.exe -v -store my" output? i.e., you've properly correlated the cert hash with the one in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate registry key?

Thanks,
Il-Sung.

|||II-Sung,

We have the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate, but it has no value. We also have the cert hash data, but what syntax should the key be in?

If the cert hash is supposed to automatically appear in this registry key, it does not. When the key value is changed to match the thumbprint (cert hash (sha1)) of the certificate then the MSSQL service will not start. When the value is blanked the service starts as it should.
|||Here is the error we recieve from asp.net 2.0 when Encrypt=True...

"System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at Security.GetSystemData() in C:\Inetpub\screen\App_Code\Security.vb:line 109
at Security.SecurityCheck() in C:\Inetpub\screen\App_Code\Security.vb:line 94"
|||

Orcabelle,

(1) To configure the server hash in sqlserver 2005, you need to put the ascii form of the Thumbprint in to the registry mentioned above, without any spacec in between and before and after, for example "06065fbcc0fd1b45b04dd67fd236673a9aa6563c". If the certificate specified in the hash is not a valid one from the point a view of sql server, sql server will fail to started. If your case if later, you can look the errorlog, look for certificate related and find what is the root cause.

(2) If you force encryption in the client, asp2.0 in your case, and that the server happens to use a self-signed certificate or a certificate that is not trusted by the client, you will see the error "The certificate chain was issued by an authority that is not trusted". In such case, in order to still use encryption, you can add a connection string property,

"TrustServerCertificate = true", such that client does not validate certificate hierarchy.

http://msdn2.microsoft.com/en-US/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx

SQL book online, "Using Encryption Without Validation" in SQL Server 2005 Books Online." also have related info.

|||So after speaking with Microsoft, we have determined the issue, but we're still awaiting a possible resolution.

The problem we've run into is caused by our using both an internal (abc.local) and external (xyz.com) domain. All this time, we had been using the FQDN for our external domain, but this wasn't being recognized by SQL Server 2005. As soon as we created a certificate using the FQDN for our internal domain, it was recognized.

That said, we still don't know for sure whether client based encryption is possible using SQL Server 2005 with valid but different named domains. We have tested and server based encryption does seem to work, but we still want to know why client based does not. It should be a two way street or so they say.

Anyway, thank you all for the help you provided. I will post Microsoft's response/recommendations once we hear from them.

Have a good day.

-Matt

CC and SSN encryption

All,
Is there any law enforced by regulatory bodies to encrypt
1.Credit Card info
2. SSN
I need some document on it, which I can use for my presentation to my
management. Any US Bill's text can also be usefull.
TIAThe following is a FAQ concerning the PCI and CISP standards for encrypting
CC numbers.
http://www.patownsend.com/VisaPCI-CISP.htm
Federal legislation such as SOX, HIPAA and GLBA also have regulations for
how such information is stored and how it can be shared with 3rd parties.
However, some of these regulations, such as SOX compliance, only apply to
publicly held companies.
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OrmvCxc%23FHA.2608@.tk2msftngp13.phx.gbl...
> All,
> Is there any law enforced by regulatory bodies to encrypt
> 1.Credit Card info
> 2. SSN
> I need some document on it, which I can use for my presentation to my
> management. Any US Bill's text can also be usefull.
> TIA
>|||It can depend on what type of data it is. For example, if these are SSN in
medical records or health care claims, then HIPAA regulations would be in
effect. There can be other industry specific regulations such as banking,
etc. I don't know of any general encryption requirements in any law for the
storage of data.
Usually if there is a law, it requires the keeper of the data to protect
access to the data. Other laws such as HIPPA go a bit futher and require yo
u
to log who, what, where, when and how the data was accessed.
Hope that helps,
Joe
"Kay" wrote:

> All,
> Is there any law enforced by regulatory bodies to encrypt
> 1.Credit Card info
> 2. SSN
> I need some document on it, which I can use for my presentation to my
> management. Any US Bill's text can also be usefull.
> TIA
>
>|||Basically data is related to eLearning. So what Law says about this domain?
-Kay
"Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
news:2FF1EDD3-0070-4106-AD4E-3A43C591250F@.microsoft.com...
> It can depend on what type of data it is. For example, if these are SSN
> in
> medical records or health care claims, then HIPAA regulations would be in
> effect. There can be other industry specific regulations such as banking,
> etc. I don't know of any general encryption requirements in any law for
> the
> storage of data.
> Usually if there is a law, it requires the keeper of the data to protect
> access to the data. Other laws such as HIPPA go a bit futher and require
> you
> to log who, what, where, when and how the data was accessed.
> Hope that helps,
> Joe
> "Kay" wrote:
>|||Basically data is related to eLearning.
-Kay
"Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
news:2FF1EDD3-0070-4106-AD4E-3A43C591250F@.microsoft.com...
> It can depend on what type of data it is. For example, if these are SSN
> in
> medical records or health care claims, then HIPAA regulations would be in
> effect. There can be other industry specific regulations such as banking,
> etc. I don't know of any general encryption requirements in any law for
> the
> storage of data.
> Usually if there is a law, it requires the keeper of the data to protect
> access to the data. Other laws such as HIPPA go a bit futher and require
> you
> to log who, what, where, when and how the data was accessed.
> Hope that helps,
> Joe
> "Kay" wrote:
>|||Basically data is related to eLearning. So what Law says about this domain?
-Kay
"Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
news:2FF1EDD3-0070-4106-AD4E-3A43C591250F@.microsoft.com...
> It can depend on what type of data it is. For example, if these are SSN
> in
> medical records or health care claims, then HIPAA regulations would be in
> effect. There can be other industry specific regulations such as banking,
> etc. I don't know of any general encryption requirements in any law for
> the
> storage of data.
> Usually if there is a law, it requires the keeper of the data to protect
> access to the data. Other laws such as HIPPA go a bit futher and require
> you
> to log who, what, where, when and how the data was accessed.
> Hope that helps,
> Joe
> "Kay" wrote:
>|||I don't see how SSN and credit card numbers could be related to eLearning;
except perhaps in a peripheral way when the user pays for the service, but
that would be more related to eCommerce.
"Kay" <CallDBA@.hotmail.com> wrote in message
news:e$V2GUm%23FHA.1032@.TK2MSFTNGP09.phx.gbl...
> Basically data is related to eLearning. So what Law says about this
> domain?
> -Kay
> "Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
> news:2FF1EDD3-0070-4106-AD4E-3A43C591250F@.microsoft.com...
>
>|||Just like Jay, I don't see what SSN and CC have to do with eLearning.
If you are accepting credit cards for payments, you may be bound by your
credit card agreement. I don't know of any federal law relating to
eCommerce. Now, if you're a financial instition, credit card company, etc.,
that's a whole other story.
I suggest you contact the financial institution that services your merchant
account or the company that handles your credit card processing for specific
rules.
BTW, I coded an ecommerce site that used a third party credit card
processing company. All we stored in the database was the last four digits
of the cc number and the approval code. The secured web pages, cc
processing, etc. took place at the third party site.
If there is a law either now or in the future, it will undoubtly be like
HIPPA. You'll have to have comprehensive written procedures outlining how
you protect confidential information from access to the physical hardware
(how do you control who enters the computer room? do they have to swipe a
badge in and out? etc.), how you handle backup media? are tapes are stored i
n
a bank vault or secure location?, network security (each user has a separate
login with a strong password?), database security, table security, column
security, stored procedure execution rights, etc. Who can access the data,
how do they access the data, when do they access it (i.e. audit log of who
accesses cc, ssn, etc., when, how, for what purpose.) If you encrypt data,
how do you do it? Do you use keys? Where are the keys kept? How often do
you review internal procedures and training of personal?
An example is: User \\Wkstn1\JDoe ran stored procedure usp_Select_All_CC on
12/05/2005 at 1:45 PM at ip address 192.168.1.101 using application "Credit
Application".
Get the jist? Most laws require you to prove that you were taking
reasonable precautions to protect and safegaurd the data. Treat ssn, cc,
etc. like you would salary information. Would you want your salary in a
table that anyone could access on the server by running a simple query?
Probably not. Salary information is usually stored in a separate table,
sometimes in a separate database, and in larger companies often stored on a
separarte computer. Usually only authorized people are allowed to access
salary information and usually only for "approved" purposes or bonifide
business reasons--not just because they are curious about what someone is
making.
Hope that helps,
Joe
"Kay" wrote:

> Basically data is related to eLearning. So what Law says about this domain
?
> -Kay
> "Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
> news:2FF1EDD3-0070-4106-AD4E-3A43C591250F@.microsoft.com...
>
>