Empty Column: created

Showing posts with label created. Show all posts

Thursday, March 29, 2012

Endpoints and authentication

I tried asking a similar question over at the asp.net, but I'm not getting any replies.

I created an endpoint in SS 2005 using DIGEST authentication, and I was successful in adding the web service to my project and getting results from a call to it.

However, the production environment does not exist in a domain environment, which eliminates even DIGEST (which requires a valid windows domain logon).

But, when I create the endpoint using BASIC authentication, I can no longer "find" the service. SS says the command(s) completed successfully after the Create Endpoint command. As a test, the documentation says that you can enter the http site into IE and the WSDL will display. And that works in digest mode. However, I've tried both:
http://<server>/path?WSDL and
https://<server>/path?WSDL
And neither returns the WSDL in IE (nor can it be added to my project as a web service).

I'm hoping someone has some ideas on how I can resolve this problem.

TIA,
DaveI suspect that my SSL problem has to do with how the server is setup. I just tried using DIGEST authentication with a LOGIN_TYPE=MIXED. This combination requires PORTS=SSL also.

I go the same messages when I tried to attach. Firefox reports the error as "The connection was interrupted" IE says "Internet Explorer cannot display the webpage"

Could someone give a few hints on what to check on my server?

TIA,
Dave

Endpoint wont deploy

im just playing with endpoints, and i have created this one below:

create endpoint my_Endpoint

state = started

as http(path='/sql',

authentication=(INTEGRATED),

PORTS=(CLEAR),

SITE='endpointTest')

For SOAP

(

webmethod 'GetSprocData'(name='adventureworksdw.dbo.testEndPointSproc'),

webmethod 'GetFunctionData'(name='adventureworksdw.dbo.endpointFunctionTest'),

wsdl=default,

schema=standard,

database ='adventureworksdw',

namespace = 'myNamespace'

)

The problem here is that although the script executes successfully, the site is not created within IIS and thus the endpoint is not deployed. Im using windows vista, and have IIS installed. does anyone know what im doing wrong?

The SQL Server 2005 SOAP/HTTP endpoints do not require IIS; therefore, they will not show up on the IIS management tool. You do not need IIS installed to create these endpoints.

There are couple of ways to verify that the endpoint has been successfully created. Since you have enabled WSDL generation on the endpoint, you can use an Internet browser (such as IE) to retrieve the WSDL document. In your specific scenario the URL will be http://endpointTest/sql?wsdl. Alternatively, a bit more complicated way is open a command prompt and run "tasklist" and you should see an entry for SQL Server example:

Image Name PID Session Name
========================= ======== ================

sqlservr.exe 3104 Services

Then you can run "netstat -ano" and look for the set of ports that SQL Server is listening on:

>netstat -ano

Active Connections

Proto Local Address Foreign Address State PID
TCP 0.0.0.0:80 0.0.0.0:0 LISTENING 3104

I recommend reading up on the implications of the "SITE" keyword value setting on the endpoint. Detailed information is available on MSDN (http://msdn2.microsoft.com/en-us/library/ms181591.aspx)

For testing purposes, I recommend setting the "SITE" keyword value to "*".

Jimmy

Endless looping job!

i have created a job that i have scheduled to run every 10 min everything is configured well since i have tested preety everything their is to be tested and found that it was my last step wich as a fetch in it so i imagine that this fetch is making it loop over and over again. the job goes trought all the steps and starts back at the first step and keep going like that till i disable it here is my fetch statement and if you have any clue any help would be widely apreciated.

PS: i suspected it to be that fetch statement causing the havoc ;)

DECLARE
@.TransactionNb varchar(10),
@.EqId varchar(10)

DECLARE TransactionNb_cursor CURSOR
FOR
SELECT TransactionNb, EqId
FROM DetCom
WHERE UpdCode = 'C'

OPEN TransactionNb_cursor

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId

WHILE @.@.FETCH_STATUS <> -1
BEGIN
-- Vrifier s'il existe une transaction avec le UpdCode = 'C' dans EntCom
IF (SELECT UpdCode
FROM EntCom
WHERE TransactionNb = @.TransactionNb and EqId = @.EqId) = 'C'
BEGIN
CONTINUE
END
ELSE
BEGIN
RAISERROR (50006, 10, 0, @.TransactionNb, @.EqId)
END

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId
END

CLOSE TransactionNb_cursor
DEALLOCATE TransactionNb_cursorChange the loop terminator to WHILE @.@.FETCH_STATUS = 0
not <> -1

Thursday, March 22, 2012

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Back up and restore database on another server, decrypting the data results in NULLs being returned. Backed up and restore the master key, cerficate, etc but either it completes successfully or SQL states that the old and the new keys are the same and wont be changed. Master key opens (using password), cert opens (is viewable in sys.openkeys) but then closes automatically after trying to decrypt. Suggests there is something wrong with the certificate or the key, but I have no idea what it is.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

When you restore the database on the other server, you will need to add the service master key encryption to the database master key. You don't need to individually backup and restore anyhting else (don't worry about individually restoring the master key and the certificate). See this post for additional information on the master keys: http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx.

If you opened the master key using a password, things should also work with no change.

But the certificate cannot be "opened" and it never shows in sys.openkeys. What you need to open is the symmetric key and this should be done following the same steps that you did in the original database.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Tomorrow I will run through the procedure again using new databases, and post all the scripts I used for the encryption and restoration on the second server (it wont be pages full, merely one line per activity and someone else might get benefit from it as well)

|||

This ALTER MASTER KEY statement is the only thing you'll need to issue to complete the restoration of the database. Everything else should already be recovered with the database itself. Let us know if you hit any issues.

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Repeated the restore again with another database, same steps, same result, NULLs when decrypting. The key is open and in sys.openkeys (cant open the key without opening the master key first), try to use the decryptbykey and the are no values returned (key remains open). Restarted SQL, key opens fine but still NULLs get returned but now the key gets closed automatically after the first decryptbykey attempt.

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

The behavior that you describe is very strange indeed. Could you post the T-SQL that you used to open the key and to decrypt? I will try to repro this behavior, but because of holidays, I may not be able to do this very soon.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

We have tried the scenario that you describe, moving a database between a Windows 2000 and a Windows 2003 machine, and we were successful in decrypting data after restores. We were not able to reproduce the problem that you described.

You mentioned that data becomes available after a while. How much time did it took before you could decrypt? Could you post the entire script that you are executing for encrypting the data and then decrypting it after restoring the database?

I'd also suggest to try to encrypt and decrypt some value after the restore to verify if the behavior that you are noticing is also happening for data encrypted after the restore. If the encryption would fail as well, then that would indicate an issue with the key.

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

What usually happens with the data is that is becomes available the next morning when I am back at the office, after playing with it in the afternoon and not getting any results. I have tried things like restart SQL and reboot the server to see if that would speed up the recovery, but it didnt help anything. Nothing happens in the server during that time, I just get in, open the symm key and it decrypts.

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

I retried this after the upgrade from Dev to Enterprise, but I still had the problem occuring. I am able to open the key (appears in sys.openkeys) but inserting any values into the 2000 database just insert NULLs into the encrypted column. So the key doesnt seem happy to do encryption or decryption.

I then backed up the database on the 2000 server, and restored it again on the 2003 server (where it came from originally) under a new name. Again I opened the master key and did the service master key change, opened the symmetric key and was able to view the information. So yeah, it seems everything is fine on the database side, just something on the 2000 set up is causing some issues.

This is a bit of a mystery, I have left the machines and will check it tomorrow morning to see if the 2000 machine magically starts to decrypt again. There arent any jobs or policies on them and they are purely standalone.

Is there any trace or debugging that I can enable that might give some more info?

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

If you opened the master key using a password, things should also work with no change.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

Is there any trace or debugging that I can enable that might give some more info?

sql

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

If you opened the master key using a password, things should also work with no change.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

Is there any trace or debugging that I can enable that might give some more info?

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

If you opened the master key using a password, things should also work with no change.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

Is there any trace or debugging that I can enable that might give some more info?

Encryption and database restore

Can anyone help?

I have a database using encryption with symmetric keys created using asymmetric keys.

I backed up this database and restored it on another machine along with the service master key.

I can read the encrypted data fine. But all the encrypted stored procs that are associated with the application that uses the data cannot read the data, they just return Nulls.

If I regenerate all the encrypted stored procs from script they work fine again.

Is there a way to restore the database without having to regenerate all the encrypted procs?

You are probably missing this sequence after you restore the database:

1) open master key using password

2) ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Your script probably creates the database master key too, so you end up recreating it with an encryption of the service master key, and everything works, but you only need the two commands above, to make your database work after restore.

See http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx for additional information on the master keys.

Thanks
Laurentiu

|||Thanks will give it a try and get back to you|||

Well now I am really puzzled. I created the same database on 2 machines then restored both databases back on to a third machine. I can read the eccrypted data from one of the backups but not from the other. All 3 machines are running XP and SQL 2005

On the both databases with encrypted data I ran the following

OPEN MASTER KEY DECRYPTION BY PASSWORD = asecretpassword

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Then ran the script to read my encrypted data

Just done some more checking. If i take a database that I created on one machine and can read on another then restore it onto the 3rd then I cannot read it on the 3rd.

(This is also the machine I created the database on that I could not read on other machines)

When I try to open my symmetric key I get the following error

This is after opening and altering the master key as above

Msg 15466, Level 16, State 1, Line 1

An error occurred during decryption.

It seems that their is something incompatable on the third machine yet it is running the same version of OS and SQL as the other machines

|||

Are your machines in the same time-zone? Is the time properly synchronized between them? There is a known issue with restoring databases on a machine that has the time set "in the past". See page 2 of this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177863&SiteID=1. But I'm not sure you're hitting this issue.

Can you post the statement that you execute before getting the error? Also, can you tell me some details about the key: what algorithm are you using and how did you create the key.

Just to make sure I understand the scenario: what you see is that whatever you encrypt on the third machine, you cannot decrypt on the other machines, and what you encrypt on the other machines, you cannot decrypt on this third machines. Otherwise, for the other machines, encryption and decryption works ok when restoring databases from one to another. Is this correct?

Thanks
Laurentiu

|||

Also, can you execute winver on all three machines and post the version line including the build number information for all three of them? I just need the line under Microsoft Windows.

Thanks
Laurentiu

|||

Hi Laurentiu

I have not tried writing encrypting any data on the databases I cannot read encrypted data from, I am more concerned with being able to read the data but I doubt that I would be able to write data as the errors i get indicate that the asymetric keys cannot be opened.

All machines are in the same time zone and domain except the 2003 server which is in Holland whilst the rest are in the UK and we are not using certificates. So I dont think it is a time zone thing

I have created another database build on a 4th machine running 2003 Server and copied a backup of that database onto my XP machines and I cannot read the encrypted data from the 2003 backup on any of the XP machines I have tried this on.

However if I restore the master key from the any machine where the database was created with the force option I can read the data.
The restore of the master key gives me a message saying that it could not open the asymmetric keys

I run the following bit of code to read the data

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Asecretpassword'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

This appears to run OK

Open Symmetric Key NoKeySymetricKey
Decryption by ASYMMETRIC KEY NoKey

SELECT top 10
CONVERT(VARCHAR(50),
DecryptbyKey(EncryptedNo)) AS ClearNumber
From MyTable

Close Symmetric Key NoKeySymetricKey

I get the following error

Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.

(10 row(s) affected)
Msg 15315, Level 16, State 1, Line 8
The key 'NoKeySymetricKey' is not open. Please open the key before using it.

Winver from the machines I have tried this on

(Mine)
Version 5.1 (Build 2600.xpsp_sp2_grd.050301-1519 : Service Pack 2)

(Andrews)
Version 5.1 (Build 2600.xpsp_sp2_grd.050301-1519 : Service Pack 2)

(Chris's)
Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2)
Can backup and restore between above machines OK but not restore from the 2 below

(Build Machine)
Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2)
The database built on this machine cannot be read on the first 2 of the 3 above machines;
have not tried it on the server below or on the machine runing Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2) (Chris's)

2003 Server
Version 5.2 (Build 3790.srv03_sp1_rtm.050324-1447 : Service Pack 1)
The database created on this machine can not be read on any of the other machines

I built the keys on all machines/databases with the same bit of code below

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'asecretpassword'

CREATE ASYMMETRIC KEY PassKey
WITH ALGORITHM = RSA_512

CREATE ASYMMETRIC KEY NoKey
WITH ALGORITHM = RSA_2048

CREATE SYMMETRIC KEY NoKeySymetricKey
WITH ALGORITHM = Triple_Des
ENCRYPTION BY ASYMMETRIC KEY NoKey

CREATE SYMMETRIC KEY PassSymetricKey
WITH ALGORITHM = Triple_Des
ENCRYPTION BY ASYMMETRIC KEY PassKey

Just tried the restore of database from another XP maxhine and it fails. If I force restore the master key it works OK. However if I try to restore without the force option I get the following message

Msg 15320, Level 16, State 2, Line 2

An error occurred while decrypting asymmetric key PassKey that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable.

|||

So it looks like the asymmetric keys cannot be decrypted unless you force restore the master key. It's strange that the master key appears to be valid, given that you receive no error when you try to open it.

Can you try encrypting something with the master key immediately after restore? Try creating another asymmetric key, then use that asymmetric key to encrypt something and see if you get any errors.

I'd also like to know if the same problem affects certificates.

Also, note that the timezone issue that I mentioned earlier affects symmetric keys, it's not certificate related. But now I'm pretty positive you're seeing a different issue.

Here's something else I'd like to ask: can you create a simple test database with a master key (password = BugCrypto90, let's say), an asymmetric key, and a symmetric key, on both your machine and the build machine. The databases should not contain any other data and you should see the same issue for them when trying to restore them on the other machine (i.e., opening the symmetric key should fail with a decryption error). Then submit a bug report at http://lab.msdn.microsoft.com/productfeedback/, and attach compressed backups of these two databases to the bug. Add a link to this thread in the description and ask for the bug to be assigned to me. If you use a different password for the keys, mention it in the description as well. This way, I can also try to reproduce this problem on our machines.

Thanks
Laurentiu

|||

Hi Laurentiu

I will submit a bug report as asked.

However in the meantime I tried creating a new asymetric key on my restored database and encrypting some data. Everything went OK I was able to both write and read encrypted data created with my newly created asymetric key even though I couldnt read my old data.

Martin

|||

Hi Again

I have logged the problem as a bug report but I dont know if you are going to be able to reproduce it.

I tried as you asked and created a test database on my machine and the build machine and created keys using the same method I used on my live database and I can backup the databases and restore them on different machines and open the symetric keys OK.

However I still have a problem with my database - will continue testing and let you know if I can re-produce it

Martin

|||

Can you also look into whether the problem is specific to asymmetric keys or can be reproed with certificates as well?

Thanks for taking the time to file the report.

Laurentiu

|||

I've received the bug report, but there are no attachments. Have you attached the test databases when you filed the report?

Thanks
Laurentiu

|||

I have attached the databases again.

|||

I do not see the attachments yet. Have you pressed the "Add Attachment" button to actually attach the files? You need to press that button after you browse-select the file.

Thanks
Laurentiu

|||

will try again

It may be the security settings on our proxy server; they are pretty tight.

If it doesnt work let me know and I will do it from home

Martin

Encryption and database restore

Can anyone help?

I have a database using encryption with symmetric keys created using asymmetric keys.

I backed up this database and restored it on another machine along with the service master key.

I can read the encrypted data fine. But all the encrypted stored procs that are associated with the application that uses the data cannot read the data, they just return Nulls.

If I regenerate all the encrypted stored procs from script they work fine again.

Is there a way to restore the database without having to regenerate all the encrypted procs?

You are probably missing this sequence after you restore the database:

1) open master key using password

2) ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

See http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx for additional information on the master keys.

Thanks
Laurentiu

|||Thanks will give it a try and get back to you|||

On the both databases with encrypted data I ran the following

OPEN MASTER KEY DECRYPTION BY PASSWORD = asecretpassword

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Then ran the script to read my encrypted data

Just done some more checking. If i take a database that I created on one machine and can read on another then restore it onto the 3rd then I cannot read it on the 3rd.

(This is also the machine I created the database on that I could not read on other machines)

When I try to open my symmetric key I get the following error

This is after opening and altering the master key as above

Msg 15466, Level 16, State 1, Line 1

An error occurred during decryption.

It seems that their is something incompatable on the third machine yet it is running the same version of OS and SQL as the other machines

|||

Can you post the statement that you execute before getting the error? Also, can you tell me some details about the key: what algorithm are you using and how did you create the key.

Thanks
Laurentiu

|||

Also, can you execute winver on all three machines and post the version line including the build number information for all three of them? I just need the line under Microsoft Windows.

Thanks
Laurentiu

|||

Hi Laurentiu

All machines are in the same time zone and domain except the 2003 server which is in Holland whilst the rest are in the UK and we are not using certificates. So I dont think it is a time zone thing

I run the following bit of code to read the data

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Asecretpassword'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

This appears to run OK

Open Symmetric Key NoKeySymetricKey
Decryption by ASYMMETRIC KEY NoKey

SELECT top 10
CONVERT(VARCHAR(50),
DecryptbyKey(EncryptedNo)) AS ClearNumber
From MyTable

Close Symmetric Key NoKeySymetricKey

I get the following error

Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.

(10 row(s) affected)
Msg 15315, Level 16, State 1, Line 8
The key 'NoKeySymetricKey' is not open. Please open the key before using it.

Winver from the machines I have tried this on

2003 Server
Version 5.2 (Build 3790.srv03_sp1_rtm.050324-1447 : Service Pack 1)
The database created on this machine can not be read on any of the other machines

I built the keys on all machines/databases with the same bit of code below

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'asecretpassword'

CREATE ASYMMETRIC KEY PassKey
WITH ALGORITHM = RSA_512

CREATE ASYMMETRIC KEY NoKey
WITH ALGORITHM = RSA_2048

CREATE SYMMETRIC KEY NoKeySymetricKey
WITH ALGORITHM = Triple_Des
ENCRYPTION BY ASYMMETRIC KEY NoKey

CREATE SYMMETRIC KEY PassSymetricKey
WITH ALGORITHM = Triple_Des
ENCRYPTION BY ASYMMETRIC KEY PassKey

Msg 15320, Level 16, State 2, Line 2

|||

Can you try encrypting something with the master key immediately after restore? Try creating another asymmetric key, then use that asymmetric key to encrypt something and see if you get any errors.

I'd also like to know if the same problem affects certificates.

Also, note that the timezone issue that I mentioned earlier affects symmetric keys, it's not certificate related. But now I'm pretty positive you're seeing a different issue.

Thanks
Laurentiu

|||

Hi Laurentiu

I will submit a bug report as asked.

Martin

|||

Hi Again

I have logged the problem as a bug report but I dont know if you are going to be able to reproduce it.

However I still have a problem with my database - will continue testing and let you know if I can re-produce it

Martin

|||

Can you also look into whether the problem is specific to asymmetric keys or can be reproed with certificates as well?

Thanks for taking the time to file the report.

Laurentiu

|||

I've received the bug report, but there are no attachments. Have you attached the test databases when you filed the report?

Thanks
Laurentiu

|||

I have attached the databases again.

|||

I do not see the attachments yet. Have you pressed the "Add Attachment" button to actually attach the files? You need to press that button after you browse-select the file.

Thanks
Laurentiu

|||

will try again

It may be the security settings on our proxy server; they are pretty tight.

If it doesnt work let me know and I will do it from home

Martin

Monday, March 19, 2012

Encrypted value shown as '?' in a column of type varchar

Dear All,

I inserted a record in table on DB created on SQLServer 2005 and found out that the one of the column values is shown as '?' instead of showing the encrypted value that I sent with the insert statement.3

............................ Can anyone tell me how to get rid of this?

Thanks and regards,

Z Z.

How are you encrypting the data and how are you retrieving it?|||Thanks for your reply. Actually I'm using only one-way encryption and seen these '?' through SQL Server Management Studio by directly viewing the table contents.|||So what are you expecting to see returned if you are using one way encryption?|||

I was expecting to see encrypted value when I opened the database table directly from within the SQL Server Management Studio. Instead I found '?' only. Anyway, I am done with it and used two encryption mechanism. Thanks a lot.

Encrypted password

when we built the login control by built in login control of vs2005 the passwords are saved in the database which is automatically created.how can i get the unencrypted form of these passwords from database.

This post talks about it in great details.

http://mishler.net/2006/04/18/AspNet+Membership+Password+Administration.aspx

hope it helps

|||actually i am a begginer and this articles didnt helped me tell me something else

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

encrypt stored proc

Is there a way to find the server name in a stored procedure?
I would like to encrypt my procedures when they are created on my clients'
databases, but I do not want them encrypted when I install them on my box fo
r
testing.
Thanks.>> Is there a way to find the server name in a stored procedure?
See @.@.SERVERNAME in SQL Server Books Online.
Anith|||is there a way to determine the server name prior to running the
WITH ENCRYPTION statement?
"Anith Sen" wrote:

> See @.@.SERVERNAME in SQL Server Books Online.
> --
> Anith
>
>|||>> is there a way to determine the server name prior to running the WITH
It is not fully clear what you are trying to achieve. In the case of a
stored procedure, WITH ENCRYPTION indicates that SQL Server encrypts the
data containing the text of the CREATE PROCEDURE statement in the system
procedure syscomments.
You will have to maintain multiple copies of the stored procedure to switch
between servers based on the server names.
Anith

Sunday, February 26, 2012

Enabling E-Mail Delivery for RS

What is required in order to schedule a report for email delivery?
I have modified the RSReportServer.config file on SQL Server and created a
subscription for a report.
I can setup the subscription but it never runs.
Is there another step that I have left out?Carl,
Do you get any error messages in the Status column on the Subscriptions page
of report designer?
Andre
"Carl Meister" wrote:
> What is required in order to schedule a report for email delivery?
> I have modified the RSReportServer.config file on SQL Server and created a
> subscription for a report.
> I can setup the subscription but it never runs.
> Is there another step that I have left out?
>|||Andre,
On the subscriptions page for the report in the status column is "New
Subscription".
The Last Run column is empty.
I have this report scheduled to run every hour but it seems like it never
kicks off.
"Andre" wrote:
> Carl,
> Do you get any error messages in the Status column on the Subscriptions page
> of report designer?
> Andre
> "Carl Meister" wrote:
> > What is required in order to schedule a report for email delivery?
> >
> > I have modified the RSReportServer.config file on SQL Server and created a
> > subscription for a report.
> >
> > I can setup the subscription but it never runs.
> >
> > Is there another step that I have left out?
> >|||Hi Carl
I think the "SQL Agent" need to run, check this.
BR Per

Sunday, February 19, 2012

Enable remote connections with an SQL server created with Visual Webdeveloper 2005

Hi there, I just recently started using visual webdeveloper SQL in general, and have been driven insane by my problem. I dont really know how to use the SQL express, but i need to enable remote connections on the database so that the membership and login will work. Essentially, i Build my entire website in visual webdebeloper 2005, and it works fine when i browse it. But when i upload it to the webserver, i find the the SQL database does not have remote connections enabled. Please tell me how i can enable remote connections, preferably through Web developer express. Thanks!

Have a look at the KB Article, it goes through turning on Remote connections to the database server.

|||thx, but i dont know how to load the sever that was made for my website, or my instance into sql express 2005 in order to use the surface editor, any idea?

Thursday, March 29, 2012

Endpoints and authentication

Endpoint wont deploy

Endless looping job!

Thursday, March 22, 2012

Encryption and recovery on standby machine

Encryption and recovery on standby machine

Encryption and recovery on standby machine

Encryption and recovery on standby machine

Encryption and database restore

Encryption and database restore

Monday, March 19, 2012

Encrypted value shown as '?' in a column of type varchar

Encrypted password

Sunday, March 11, 2012

Encrypted Database transfer problem

encrypt stored proc

Sunday, February 26, 2012

Enabling E-Mail Delivery for RS

Sunday, February 19, 2012

Enable remote connections with an SQL server created with Visual Webdeveloper 2005

Empty Column

Blog Archive

About Me