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

Can You Open A Database Created In SQL2005 In SQL2005 Express?

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

Get New Database Created Then Running Script To Created Tables / Relationships

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

Sql2005 Database Restore From Another Sql2005 Backup File Error.

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

Upgraded SQL2000 To SQL2005, SQL2005 VERY Slow

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

Sql2005 Standard To Sql2005 Enterprise Edition

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

Could Someone Decrypt This?

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

Decrypt

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

RC4 Encrypt And Decrypt

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

PGP Decrypt In DTS Routine

Dec 12, 2005

Does anyone know how to decrypt a PGP encrypted file in a DTS routine?

Thanks.
Danielle

View 4 Replies View Related

Encrypt On CLR Decrypt In SQL?

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

Encrypt In .net, Decrypt In Sql

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

Install Sql2005 32 Bit After Removing Sql2005 64 Bit

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

Migrating Sql2005 Vb Dts Package To Sql2005

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

Encrypt -> Decrypt Into SQL Server

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

Decrypt Stored Procedures

Oct 4, 2000

does anyone know how to decrypt an encrypted stored procedure???


regards,
resh

View 1 Replies View Related

How To Decrypt Encrypted UDF In SQL 2000

Aug 28, 2006

How to decrypt encrypted UDF in SQL 2000
can any body help me?

View 3 Replies View Related

Encrypt/decrypt Data

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

How To Decrypt Encrypted Procedure

Dec 9, 2004

How to decrypt encrypted procedure?Any help is thankful.

View 7 Replies View Related

Encrypt/decrypt MS SQL 2005

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

Decrypt Sql Server 2005 Sp

May 21, 2008

hi,

how can i decrypt a stored procedure in sql server 2005?
THX

View 2 Replies View Related

How To Encrypt And Decrypt My Password

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

Is There Any Way To Decrypt The Login Info?

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

Db_datareaders Can Decrypt Data?

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

Encrypt/ Decrypt Network Traffic.

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

Decrypt Sproc Returning NULL To Non DBO.

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

How To Decrypt Password Data In A Table

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

Unable To Decrypt With Restored Certificate

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

Users For Encrypt And Decrypt Purposes Only

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

How To Encrypt And Decrypt Stored Procedures?

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

Failed To Decrypt Protected XML Node

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

Failed To Decrypt Protected XML Node ...

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

Failed To Decrypt Protected XML Node

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







Copyrights 2005-15 www.BigResource.com, All rights reserved