Use SQL2005 To Decrypt EncryptedData XML Created In .Net
Apr 25, 2007
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384472&SiteID=1
I suppose that some of the answer to my question may be found in that topic, but I haven't made much progress.
I have a text field in a SQL 2000 database that contains the text output (EncryptedData) from the EncryptedXML.Encrypt(xmlDoc.DocumentElement, certificate) method in .Net 2.0 (C#). The data looks something like this:
<?xml version="1.0" encoding="utf-8"?>
<EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element" xmlns="http://www.w3.org/2001/04/xmlenc#">
<EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#aes256-cbc" />
<KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
<EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">
<EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
<KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
<X509Data>
<X509Certificate>[A Bunch of jumbled characters]</X509Certificate>
</X509Data>
</KeyInfo>
<CipherData>
<CipherValue>[A Bunch of jumbled characters]</CipherValue>
</CipherData>
</EncryptedKey>
</KeyInfo>
<CipherData>
<CipherValue>[A Bunch of jumbled characters]</CipherValue>
</CipherData>
</EncryptedData>
I have since imported the Public and Private key from the .Net app into SQL Server. I can use the EncryptByCert and DecryptByCert functions to verify that the key pair is compatible. However, I can't figure out how to apply them to the encryptedXML that my friendly .Net developers are dumping in the DB. Whenever I use the DecryptByCert function on any of the "[A Bunch of jumbled characters]" strings I just get NULL.
Does anybody have some insight?
View 4 Replies
ADVERTISEMENT
Oct 12, 2007
Can you open/use a database created in SQL2005 in SQL2005 Express?
Thanks for the help!
Max
View 4 Replies
View Related
Jun 29, 2015
trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName] Go CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
View 3 Replies
View Related
Dec 15, 2005
hi
i try to restore a bak file from another sql2005 server to my sql2005 server, but it show the error message as below :
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupackup.bak'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
pls some one can help me ???
thanks
chaus
View 62 Replies
View Related
Dec 27, 2005
I just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?
View 3 Replies
View Related
Jan 4, 2007
Hi
We have Sql2005 x64 bit standard edition server installed in windows 2003 64 bit editio server,
currently due to buisness requirements we need to have sql2005 x64 bit enterprise edition, please let me know how do i do the upgrade or change.
is it possible to retain all our custom settings in the standard edition after changing to enterprise edition.
This has to be done for our production and very critical, please help
Thanks
Samuel I
View 4 Replies
View Related
Jul 23, 2007
hi all,
i have this password column that i ve no idea how it's been encrypted... i need to come out with encrypt and decrypt function for this string :-
AE435A2BE08D1797362FF3CDD6E541AA6851819C
is it possible to decrypt this when i dont have the password string? if not possible then i will try to get the password string so that any experts here could help me to come out with the encryption and decryption function.
thanks guys
~~~Focus on problem, not solution ¯(º_o)/¯ ~~~
View 20 Replies
View Related
Aug 29, 2007
Hi,
When I run the package it gives the following error warning.
Not sure how and where to fix this.
P.S. The package runs successfully and loads data but not sure why I get this error.
Thanks
Error: 2007-08-29 06:00:13.70
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error
View 3 Replies
View Related
Jul 22, 2002
Hi Guys.
I am looking for TSQL code for RC4 encryption and decryption.
I found VBcode for RC4 encryption, it will take atleast 2 days for me to go thru that and rewrite the whole code in SQL.
If anyone has the code, please provide me.
(I know sql does accept extended ASCII characters)
Thanks
-MAK
View 1 Replies
View Related
Dec 12, 2005
Does anyone know how to decrypt a PGP encrypted file in a DTS routine?
Thanks.
Danielle
View 4 Replies
View Related
Jan 8, 2008
Hi folks,
I'm trying to encrypt a small token of data on my client c# application and have it decrypted by SQL on the server side, the problem is i cannot find articles on the subject. I don;t really want to get involved with certificates but base the system on a simple symmetric key that is shared by both parties.
I'm attempting to use the TRIPLE_DES algorithm on both sides and thus far have used the decryptbypassphrase on the server side with the data encrypted on the client side by .Net with no initialization vector setup.
If anyone can recommend any articles or have example (client and server side) code for this situation it would be greatly appreciated.
Many thanks in advance
Simon
View 10 Replies
View Related
Feb 29, 2008
Does anyone know what my .net app guys need to share with me if encryption was done in the .net app but decryption needs to occur in certain sql queries? I read about master keys, certificates, symmeteric keys, algorithms etc but dont know how that stuff would carry over from the framework into sql. All I know is that the algorithm is AES_256, they must be adding authentication to the encryption and i know the hash algorithm, and symmetric keys are involved. Will they be sharing certain kinds of files with me that need to be registered in the db? Will I have to use CLR if I want the two worlds to come together?
View 4 Replies
View Related
Sep 19, 2007
I am unable to install 32-bit SQL Server Integration Services on the server due to something that was left behind by the 64-bit version.
I've uninstalled SQL Server 2005 64-bit and when I try to install the 32-bit version of Integration Services, I get this error: "Failed to install and configure assemblies C:Program Files (x86)Microsoft SQL Server90DTSTasksMicrosoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -2146233087 Error message: Unknown error 0x80131501 Error descrition: FATAL: Could not find component 'Microsoft.SqlServer.Dts.Task.MessageQueueTask.ServCompMQTask' we just installed."
I can't seem to figure out how to resolve this problem with the COM+ and I can't remember if Integration Services is required.
Can anybody please advise?
View 1 Replies
View Related
Jul 8, 2007
Hello,
I have a vb program that include a dts package that has been saved to vb with sql2000 dts wizard and works very good.
Now that I upgrade my website to sql2005, this vb dts package doesn't work.
The error I get is:
Microsoft Data Transformation Services (DTS) Package
Invalid STDGMEDIUM structure
(Microsoft Data Transformation Services (DTS) Package (80040066): Invalid STDGMEDIUM structure
) (Microsoft SQL-DMO (ODBC SQLState: 42000) (80004005): [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'.)
I searched in the internet how to make dts package in sql2005 and save it to vb and found no information about it.
What Can I do to get the vb code of the dts package I create in sql2005 or how do i migrage the sql2000 vb dts package code to sql2005?
Thanks,
Kubyustus
View 4 Replies
View Related
Apr 12, 2001
Hi,
Please help!!!!!!!
Is there are ways to encrypt data in the table (SQL Server 7) and then retrieve (decrypt) this information?
Thanks
View 1 Replies
View Related
Oct 4, 2000
does anyone know how to decrypt an encrypted stored procedure???
regards,
resh
View 1 Replies
View Related
Aug 28, 2006
How to decrypt encrypted UDF in SQL 2000
can any body help me?
View 3 Replies
View Related
Nov 16, 2004
We like to secure datas.
Only a few people are autorized to read this information, but today, these informations are readable with a simple query with a query analyzer for exemple.
I'd like to encrypt datas with reversible function in one field of a table
Is there a function able to do this kind of work in SQLServer V7 or 2000 ?
View 1 Replies
View Related
Dec 9, 2004
How to decrypt encrypted procedure?Any help is thankful.
View 7 Replies
View Related
Aug 3, 2006
hi guyz!! is it posible to ecnrypt data everytime i insert it to a table and decrypt it everytime I select it using the MS SQL 2005 alone?
like for example i have this query statement below
insert username,password users values ('daimous','my_password')
what i want is every time i insert a value to the password column that value should be encrypted first.
select username,password from users
everytime i select the value of the password column should decrypted.
Thanks in advance!!!
View 7 Replies
View Related
May 21, 2008
hi,
how can i decrypt a stored procedure in sql server 2005?
THX
View 2 Replies
View Related
Feb 5, 2007
hi,
in my login form i have the password field.so i am sending password to my database table but while sending password has to be encrypted and while returning it has to be decrypted,is it possible to do in database if means please show me some example please
View 20 Replies
View Related
May 19, 2006
Hi all.
The new feature of login encryption still tortured me and my company's fellow :(
We can't use the server's profile nor server's login audit functionality.
The reason is.. they do not want any additional feature to the server even if it's just a small task.
So..
I know that self signed certi generated whenever the MSSQL server started. My question is..
1. Where is that self-signed certification. Is it loaded to memory or physical hard disk.
2. Is there any special 'store' for this self-signed certification?
I tried to find this certi from all of my store using the certutil.exe but couldn't find this certi.
3. Is there any api that find and decrypt this login info?
Thank you.
View 7 Replies
View Related
Jan 19, 2007
Hi... I want to encrypt data using a symmetric key + certificate, but it appears anyone with simply "db_datareader" can view the decrypted data? Is this correct?
Recreation steps:
1. As a System Admin, log into a SQL 2005 Database Engine.
2. Create some login that you also have access to. Ensure it does not already exist, or inherit any permissions from some existing NT-group. (From here on out, I will refer to it as the "underpriveleged user".)
3. Create a new database of any name.
4. Run the following query against the database:
CREATE TABLE [dbo].[MyTable](
[MyColumn] [varbinary](50) NULL
) ON [PRIMARY]
GO
CREATE USER [DOMAINUserName]
FOR LOGIN [DOMAINUserName] WITH DEFAULT_SCHEMA=[dbo]
sp_addrolemember 'db_owner', 'DOMAINUserName'
5. Now open another SSMS and connect as that underpriveleged user.
6. Change the database to the name you provided in Step 3.
7. Run these commands as the underpriveleged user...
CREATE MASTER KEY ENCRYPTION BY PASSWORD='DbMK_pwd'
CREATE CERTIFICATE MyCertificate WITH SUBJECT='Some Text'
CREATE SYMMETRIC KEY MyKeyName
WITH ALGORITHM = DESX
ENCRYPTION BY CERTIFICATE MyCertificate
OPEN SYMMETRIC KEY MyKeyName
DECRYPTION BY CERTIFICATE MyCertificate
INSERT INTO MyTable(MyColumn)
VALUES (EncryptByKey(Key_GUID('MyKeyName'), 'MyValue'))
SELECT Convert(varchar,DecryptByKey(MyColumn)) FROM MyTable
CLOSE SYMMETRIC KEY MyKeyName
8. Now switch back to the System Admin session, and revoke most of the underpriveleged account's permissions...
sp_droprolemember 'db_owner', 'DOMAINUserName'
GRANT SELECT ON dbo.MyTable TO [DOMAINUserName]
9. Switch once again to the underpriveleged session, and run the following:
OPEN SYMMETRIC KEY MyKeyName DECRYPTION BY CERTIFICATE MyCertificate
SELECT *, Convert(varchar,DecryptByKey(MyColumn)) FROM MyTable
CLOSE SYMMETRIC KEY MyKeyName
Why does this work? The user only has select access against the table, and no explicit permissions to the certificate or key. I have even disconnected as this user, tried again, restarted SQL Server... it is still able to decrypt the text.
Or what should I be doing to ensure only those with access to the key/certificate can decrypt the cipher text?
View 3 Replies
View Related
Aug 8, 2006
I can sucessfully encrypt/ decrypt 1 column, but Im under the impression there is also a way to encrypt the data being sent over the network by using a certificate? I can find lots of info, but no starting point or clear cut instructions. Could someone please assist?
TIA, cfr
View 2 Replies
View Related
Nov 28, 2006
I'm still having issues with this despite my attempts to resolve. I even
have "with exec as dbo" in my sproc, and and "exec as dbo" in my execution,
but still the encrypted data returns nulls when I exec as a user other than
DBO. Below is precisely what I have done. All ideas are welcomed.
TIA, ChrisR
--If there is no master key, create one now
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117
r$$#1946kcj$n44ncjhdlj'
GO
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
USE [AdventureWorks];
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
alter procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
GRANT IMPERSONATE ON USER:: dbo TO test;
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/
View 1 Replies
View Related
Jun 4, 2014
I have a set of Password data in a table which is encrypted e.g. UOTYoeUK8ae89IM6PKButX5ssew= , i was wondering how to decryted it so that it reveals the passwords.
View 2 Replies
View Related
Jan 6, 2014
I submitted the following T-SQL:
--BACKUP CERTIFICATE EncryptTestCert
-- TO FILE = N'c:backupEncryptTestCert.cer'
-- WITH PRIVATE KEY
-- ( FILE = N'c:backupEncryptTestCert.pvk',
-- ENCRYPTION BY PASSWORD = N'T0yp0calypse'
-- )
[Code] .....
However, the return data just contains nulls, instead of the original decrypted data. You can see above that I deleted the certificate, but then restored the certificate from backup. However, it doesn't work.
View 2 Replies
View Related
Jan 13, 2006
Hi there,
Using symmetric keys and certificates in SQL2005, can one assign users permission to only decrypt or encrypt data?
Reason would be say data capturer and data reader type roles. I tried to create some with the GRANT CONTROL and GRANT VIEW for certificates and definitions on Symmetric keys, but havent been to successfull.
Would be great if someone here can offer some advise on it, and if it's possible using SQL rights.
thanks
View 6 Replies
View Related
May 7, 2006
I encrypt my procedures using with encryption clause, but I do not how to decrypt again.
Is there a command or utility for encrypt and decrypt in Sql 2000? How about Sql 2005?
Thanks
Haydee
View 12 Replies
View Related
Jun 27, 2007
Hi,
I,ve been searching the forum for answers to this error but with no luck:
Failed to decrypt protected XML node "DTSassword" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
Setup:
I'm running the packages from the SQL Job Agent - the packages are stored in the file system. The agent is using a proxy account to get the right permissions. I know this because the job has run for severel weeks without errors. The package is calling other packages and is using configuration files. It was actually more than on job that failed (with the same error) - but not all the jobs.
Now it is saying that it can not "find the file specified" - what file would that be? - I'm wondering if it is a package file or a configuration file or maybe another file. It dosn't give me any other information to where the problem is.
Any help is appreciated!
Regards
Simon
View 6 Replies
View Related
May 12, 2008
Hi All,
I am trying to run a job and when I run it I get the following error:
Description: Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-05-06 09:37:58.32 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "SQLPassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
I'm not sure what it means or why it is happening.
Any help would be appreciated.
Thank you.
View 2 Replies
View Related
Dec 6, 2007
Hi
I have a package that runs fine however it keeps giving me this message below. Now from a previous post it mentions it is to do with the EncriptedSensitiveWithUserKey what would be the suggestion to run it as Don't save sensitive perhaps?
Executed as user: SEA-SRV-00009SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:07:29 PM Error: 2007-12-05 22:07:29.78 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 10:07:29 PM Finished: 10:07:33 PM Elapsed: 4.188 seconds. The package executed successfully. The step succeeded.
View 1 Replies
View Related