SQL Server 2008 :: How To Check If The Database Itself Is Encrypted Or Only The Backups

Jul 9, 2015

I queried sys.databases in one of the sql server and found "is_encrypted" is "1" for four of the databases.

Does that mean that all those 4 DBs are encrypted ?

How to check if the database itself is encrypted or only the backups ?

Note :- I can see backups of key in a particular folder.

View 9 Replies


ADVERTISEMENT

SQL Server 2008 :: How To Check Progress Of Database

Jul 18, 2012

Our database crashed this morning and went into recovery mode.how I can track the progress of the recovery to determine how long it might take?The error log shows that it started up all the databases, then shows the recovery messages fr the msdb database, then shows that sql server is ready for client connections. I don't see any messages about my database recovery or the number of transactions to roll forward or background. If i run the sys.sp_readerrorlog and search for my database name, the only line returned is the starting up database message.

I do expect the database to take a while to recovery as it is about 8TB, there is plenty of free disk space about 3TB.The database started recovery while a transaction log backup was running so that backup failed,the last transaction log backup was taken 2 hours before recovery started. The last full backup completed about 5 days ago. The transaction log backup occurs every 2 hours and is typically around 16GB.

View 9 Replies View Related

How To Check For Failed Database Backups

May 8, 2007

Hello everyone! Looking for some insight here on database backups that fail.

We have many SQL servers that we maintain by storing Job/Maintenence Plan history on a central server, which then emails out daily reports to let us know what backed up last night and what didn't

This was easy to do in SQL 2000, not so much in SQL 2005. I have put together a query that gathers the info I need for the successes:

SELECT DISTINCT '00000000-0000-0000-0000-000000000001' AS Plan_ID, mpld.line1 as "Plan Name", bud.database_name as "Database", mpld.server_name, 'Backup Database' as Activity, mpld.succeeded, bs.backup_finish_date, DATEDIFF (MS,bs.backup_start_date,bs.backup_finish_date) as Duration, bs.backup_start_date, mpld.error_number, mpld.error_messageFROM msdb.dbo.sysmaintplan_logdetail mpld
INNER JOIN msdb.dbo.backupset bs
on (select convert(char(12),mpld.start_time,109))=(select convert(char(12),bs.backup_start_date,109))-- on bs.database_name=bud.database_name
inner join
msdb.dbo.bu_dbs bud
on bs.database_name = bud.database_name
WHERE mpld.succeeded = 1 and mpld.line2 like 'Backup%' and bs.type='d'
and bs.backup_start_date > ( SELECT CONVERT(char(12), (GETDATE()-1), 109) )
ORDER BY bud.database_name DESC

But I am having trouble using a query to determine the databases the FAILED during backup. MSDB.BackupSet and MSDB.SYSMaintPlan_LogDetail really have nothing,because often times, even if a step in a Maint. Plan fails, the plan finishes reporting success.

Does anyone know of a good way to gather info about failed database backups?

Thank you!

Mario

View 6 Replies View Related

SQL Server 2008 :: Using Encrypted Value In Select-where

Mar 6, 2015

The passwords in table 'users', column 'passwordencrypted' are encrypted. Someone enters a password and I'd like to compare if it is correct. The syntax below seems ok but nothing is returned. Why not?

OPEN SYMMETRIC KEY mykey DECRYPTION BY CERTIFICATE mycert;
DECLARE @mypw varchar(300);
SET @mypw = 'test';
SELECT * FROM users WHERE passwordencrypted = EncryptByKey(Key_GUID('mykey'), @mypw);
CLOSE SYMMETRIC KEY mykey;

View 3 Replies View Related

SQL Server 2008 :: How To Remove ENCRYPTED From A FUNCTION

Aug 27, 2015

I have a Table-valued Function already defined in my DB. When I look at it's Properties, it lists Encrypted TRUE.

How can I set Encrypted to FALSE? Is there a t-sql command to ALTER the Function to set Encrypted False?

(fyi, I am syadmin and local admin on this SQL 2008 R2 box)

View 1 Replies View Related

Problem Restoring Database With Encrypted Columns To Different Database Or Server With Encrypted Columns.

Jan 23, 2006

I need to start encrypting several fields in a database and have been doing some testing with a test database first. I've run into problems when attempting to restore the database on either the same server (but different database) or to a separate server.

First, here's how i created the symmetric key and encrypted data in the original database:

create master key
encryption by password = 'testAppleA3';

create certificate test
with subject = 'test certificate',
EXPIRY_DATE = '1/1/2010';

create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;

open symmetric key sk_Test decryption by certificate test;

insert into employees values (101,'Jane Doe',encryptbykey(key_guid('sk_Test'),'$200000'));
insert into employees values(102,'Bob Jones',encryptbykey(key_guid('sk_Test'),'$500000'));

select * from employees
--delete from employees
select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from employees

close all symmetric keys

Next I backup up this test database and restore it to a new database on a different server (same issue if restore to different database but on same server).

Then if i attempt to open the key in the new database and decrypt:

open symmetric key sk_Test decryption by certificate test;

I get the error: An error occurred during decryption.

Ok, well not unexpected, so reading the forums, i try doing the below first in the new database:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Then I try opening the key again and get the error again:

An error occurred during decryption.

So then it occurs to me, maybe i need to drop and recreate it so i do

drop symmetric key sk_test

then

create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;

and then try to open it.

Same error!

So then i decide, let's drop everything, the master key, the certificate and then symmetric key:

drop symmetric key sk_test
drop certificate test
drop master key

Then recreate the master key:

create master key
encryption by password = 'testAppleA3';

Restore the certificate from a backup i had made to a file:

CREATE CERTIFICATE test
FROM FILE = 'c:storedcertsencryptiontestcert'

Recreate the symmetric key again:

create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;

And now open the key only to get the error:

Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.

So what am I doing wrong here? In this scenario I would appear to have lost all access to decrypt the data in the database despite restoring from a backup which restored the symmetric key and certificate and i obviously know the password for the master key.

I also tried running the command

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

again but this does not resolve the issue.



Thx.

View 6 Replies View Related

SQL Server 2008 :: Script Out Tables With Encrypted Triggers?

May 14, 2015

I am trying to run a test migration of our application from V8 to V9. To do this I am creating an empty database by running the Tasks -> Script database

Once that is done, I restore it to our dev server and run SQLCompare against it to generate the change scripts

The problem I am facing is that there are a few tables that have encrypted triggers on them and I cannot include them in the generate scripts. I want to take the table structure, including indexes, but I am not interested in the triggers. There are only 5 tables so I could script them by hand, but this means that I cannot automate the process.

As a result of the missing tables SQLCompare will try to generate a CREATE TABLE rather than an ALTER TABLE DDL

The live migration won't be a problem because this will be a database restore and migration - my problem is the CREATE TABLE DDLs in the script.

View 6 Replies View Related

SQL Server 2008 :: Moving Backups / Restoring Databases

Feb 23, 2015

I am working on a task. Currently we are taking a database backup and keeping that backups in a folder. The backups doesn't have time stamp on it. My task is need to get the latest backup and copy that backups into some other server and then restore the database from there.I am planning to create SSIS package.Do we need script task for this task.How to get the .bak with latest create or moidified date. For now we doesn't have timestamp so need to go based on modified date?

View 9 Replies View Related

SQL Server 2008 :: Restore A Point Between Two Full Backups

Oct 23, 2015

I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:

1.Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past.
2. Restore the server with Oct 1 full backup with NORECOVERY option.
3.Try to restore to the point at Oct 5 12:00, with the transaction log.

But the restore fails and SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?

View 4 Replies View Related

SQL Server 2008 :: Backup Device Creating Backups With A New Transaction Log For Each Day

Jun 19, 2015

Having a lot of problems with backup device creating backups with a new transaction log for each day. This is causing the backups to grow way to fast. Seems to be random with our clients. Created new device backups but getting same problem. A manual backup selecting overwrite all existing backup sets will fix it. But starts the cycle all over again.

View 9 Replies View Related

SQL Server 2008 :: How To Check A Path Existing

Apr 29, 2015

whenever I use the below script, it throws the below error.

declare @DbFilePath VARCHAR(max)
set @DbFilePath = ' c:mdndndmmnsn'
if object_id('tempdb.dbo.#fileExist') is not null
drop table #fileExist

[Code] ....

Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.

View 2 Replies View Related

SQL Server 2008 :: Error 23 - Redundancy Check Failed

Feb 2, 2015

Automated and manual backup done through SQL Management console are failing with error 23 - redundancy check failed.

This is a critical production db. Other db in same instance backs up ok.

Is there a way to fix this?

View 4 Replies View Related

SQL Server 2008 :: Check DB Mail Status On All Servers

Apr 23, 2015

i have 70 SQL database servers and i setup DB Mail on the 70 Servers, i want to know is there a way to find the status of all the jobs which i assigned the DB Mail and if its working/failing... is there a script i can run on powershell or SQL to find out that information

View 1 Replies View Related

SQL Server 2008 :: How To Check Page Split Number

May 11, 2015

I am working now on optimization of an update query for a particular table and I want to measure the number of page splits after each update. How to check it?

View 6 Replies View Related

SQL Server 2008 :: Check Constraint On Group Of Records?

May 25, 2015

I have groups of records in a table, and I would like to set a necessary condition on each group. The condition is that EXACTLY ONE of the records in each group has a flag field set to True (bit = 1). I can naturally write triggers for update, insert and delete events that test for such a condition.

Something along the lines of this condition:

(select count(ClovekAutoID)
from TableOfClovekNames tCN
where JeHlavni = 1
group by ClovekAutoID
having COUNT(JeHlavni ) > 1) = 1In fact,

I tried this just on whim, but naturally, the SS engine told me to go roll my hoop, that subqueries are not allowed in constraint expressions.

View 9 Replies View Related

SQL Server 2008 :: Function To Check Name Format (Last / First / Middle)

May 27, 2015

I have to figure out the items that Legal Name implies individual but Legal Entity Structure indicates a incorporation type. In this sample, you can see Alexander, Justin N. is my target. But my problem is how should I use a query to figure out which one is a individual's name? How should I write a function to check the name format (Last, First Middle)?

Legal Name ////////////////////////////////////// Legal_Entity_Struct

S & H Farm Supply, Ltd.////////////////////////////Company
F.M.Abbott Power Equipment,Co.///////////////Company
Ray's Dixie Chopper, Inc.////////////////////////// Company
Alexander, Justin N. ///////////////////////////////// Company
Alameda Power Equipment, Inc.//////////////// Company

[Code] .....

View 0 Replies View Related

SQL Server 2008 :: Check For Missing Rows In A Table

Oct 20, 2015

I have a table of languages, identified by a lang_id column as follows:

LANG_IDLANG_NAME
deDeutsche
enEnglish
plPolski
trTurkish
czCzech

I also have a RESOURCES table of phrases (for translation purposes) similar to this:

res_id res_lang res_phrase
AccessDenied en Access Denied

For some rows in the resources table I do not have all language codes present so am missing some translations for a given res_id.My question is what query can I use to determine the RESOURCE.RES_IDs for which I do not have a translation for.

For example I might have a de, en, cz translation for a phrase but not a pl phrase and I need to identofy those rows in order that I can obtain translations for the missing RESOURCE rows.

View 6 Replies View Related

SQL Server 2008 :: Query To Check Downtime In Production Lines

Feb 3, 2015

I'm trying to run a query to check the downtime in production lines, but if a line has assigned more than one cause for the downtime it repeat the info for each cause.

This is the code.

SELECT D.Line AS Line, D.ProductionLine AS ProductionLine, D.Shift AS Shift, D.DownTime, CONVERT(VARCHAR(10), D.DatePacked,101) AS DatePacked, AssignedDowntime, (D.DownTime - AssignedDowntime) AS NOASSIGNED,
R.Enviromental,R.Equipment, R.IT_Systems, R.Material_External,R.Quality,R.Material_Internal,
R.Method,R.PreProduction,R.People
FROM (
SELECT Line, Shift, DatePacked, SUM(Cast(Downtime AS INT)) AS AssignedDowntime,

[Code] ....

I'm expecting that if is more than one "Down Reason "it will include in the same line. At this moment if i have more than one reason it create a line for each one for example:

If i have a total Downtime of 50 minutes and they are assigned 10 for itequipment, 30 by testequipment and 10 assigned to quality issues i will have and output like this:

Line Total_Downttime By itequipment by_testequipment bypeopleissues byquality
line1 50 0 30 0 0
line1 50 10 0 0 0
line1 50 0 0 0 10

What i want is to have a output like this:

Line Total_Downttime By itequipment by_testequipment bypeopleissues byquality
line1 50 10 30 0 10

All in one line.

View 2 Replies View Related

SQL Server 2008 :: Check If File Exists Using Script Task

Mar 17, 2015

I need to create a script task in sql server 2008 R2 to check if a file exists in a directory. For example, to see if output.dat exist under c:results. If the file exists, then send out an email stating the file exists, if not then send out another email stating the file does not exists.I noticed there is a huge difference between the script task in sql 2005 and sql 2008 r2.

View 3 Replies View Related

Secure A Single SQL Server Database + Make Float Fields Encrypted

Mar 21, 2007

Hi all,

I am developing an application for a big office which uses SQL Server 2000.
Apart from my database, on that server, there are two databases by other companies.
The administrator also has access to server but the client only wants him to backup the database.

I have two questions:

1) First of all (if it is possible) I would like to protect my own database from the other companies.

I don't want them to:


see the data in the tables (around 20 tables)
make changes to the stored procedures (more than 100 stored procedures)
be able to backup the database

2) The client will save sensitive data to the database (mainly currency amounts, salaries etc) which he wants to keep hidden.
I am using float type for these fields and I would like to make the data encrypted. I could do it for nvarchar fields but changing these float to nvarchar would be time consuming.



Thanks for your patience reading this!

Would really appreciate some help on any of these

Nicolas

View 4 Replies View Related

SQL Server 2008 :: Way To Check To Find Number Of Rows And Size Of A Table

Apr 29, 2015

How can we monitor the all tables in all databases and send notifications to the team.Is there a way to check to find the no of rows and size of a table last month and find out growth % now

View 4 Replies View Related

Impossible To Check Reporting Services Box On Install [Windows Server 2008]

Jan 29, 2008

Hye all, running WHS 2008, I try to install SQL Server 2005 with reporting services, but on install, in "components to install" wizard, the reporting services box is hided. I can't check this box :/ Do you know whats wrong ? if anyone knows the problem, thanks in advance.

View 1 Replies View Related

SQL Server Admin 2014 :: Cannot Decrypt Encrypted Columns From Database Backup On Local Machine

Jun 29, 2015

I've a SQL server 2014 running on one of our server. We're in the process of implementing security steps for our databases. I've encrypted a column in one of the table in the database on the server. The issue is when I restore the backup on my local SQL server and run a query to decrypt the column data it gives me null values. On the other end when I decrypt the column data on the main server it works fine. I found a thread on this forum which states to do the following when restoring the encrypted database on different server.

USE [master];
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

select File_Name
, CONVERT(nvarchar,DECRYPTBYKEY(File_Name))
from [test].[dbo].[Orders_Customer]

I tried doing above still no luck.

View 3 Replies View Related

SQL 2012 :: How To Check For Successful Full Backups On Availability Group Databases

Apr 20, 2015

I am setting up Availability Groups and I want to use the secondary replica to perform the full copy_only backups to reduce the load on the primary replica.But what is the best way to check for successful full backups on Availability Group databases?

Previously I could check the system table msdb.dbo.backupset but this is not available for copy_only backups.So I wonder how people are monitoring that their full backups have been successful?

Do you just check that the SQL Agent job that runs the backup was successful?

Or do you search the SQL Server Error Log for entries like "Database backed up. Database: xxx" where database xxx is in an Availability Group?

Or is there a better method?

View 1 Replies View Related

SQL Server Database Backups

Jul 28, 1998

I am looking for the best method to backup SQL Server databases. Currently we are
running a dump database statement to disk and backing up the files to tape through Arcserve.

One problem that I am having is the statement to dump the database. I would like to retain
the dump for at least three days and be able to restore the database from any one of those
three days. My current statement is:
"DUMP DATABASE CHOISDAT TO DISK=`D:BACKUPCHOIS.BAK` WITH
NOUNLOAD , STATS = 10, INIT , RETAINDAYS = 3, NOSKIP"

but, every other day I receive the message from SQL executive:
"Can`t open dump device `D:BACKUPCHOIS.BAK`, device error or device off line.
Please consult the SQL Server error log for more details. (Message 3201)"

What am I doing wrong? Any suggestions?

P.S.

Is there anyway to tell the Maintenance Wizard to delete the backups. I tried using the wizard but
the backup files still remain on the disk and I have to delete them every week.

View 1 Replies View Related

The Report Server Cannot Decrypt The Symmetric Key Used To Access Sensitive Or Encrypted Data In A Report Server Database

May 25, 2007



Hi every one,



I'm very new new at this. I'm try to deploy a report model and got this message. I have no idea what its going on about.



Can anyone help me?



Aku




------------------------------

System.Web.Services.Protocols.SoapException: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> Microsoft.ReportingServices.Diagnostics.Utilities.RPCException: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: Bad Data. (Exception from HRESULT: 0x80090005)
--- End of inner exception stack trace ---
--- End of inner exception stack trace ---
--- End of inner exception stack trace ---
--- End of inner exception stack trace ---
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.Diagnostics.DataProtection.ProtectData(Byte[] unprotectedData, String tag)
at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
at Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String storedProcedureName)
at Microsoft.ReportingServices.Library.DBInterface.GetAllConfigurationInfo()
at Microsoft.ReportingServices.Library.RSService.GetSystemProperties(Property[] requestedProperties)
at Microsoft.ReportingServices.WebServer.ReportingService.GetSystemProperties(Property[] Properties, Property[]& Values)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.WebServer.ReportingService.GetSystemProperties(Property[] Properties, Property[]& Values) (System.Web.Services)

------------------------------
BUTTONS:

OK
------------------------------

View 38 Replies View Related

Copy Database With Encrypted Column To New Server And Decrypt Column There

Aug 17, 2006

To do this successfully do I need to backup the Service master, Database master, and database itself from the the Source server, then restore all three of them on the destination server?

(I'm concerned that restoring the source Service Master key to a new target server with an existing sql 2005 install will screw things up big time.)

TIA,

Barkingdog

View 1 Replies View Related

SQL Server Admin 2014 :: Database Backups And AlwaysOn Availability Groups

Oct 16, 2014

This is my first deployment of an always on availability group for SQL 2014 and I'm trying to get my custom backup procedure to handle all databases appropriately depending on the primary group. Basiscally I want the system databases and all databases that don't participate in the availability group to be backed up on both nodes and those that do participate backed up ONLY on the primary server. I've looked at the sys.fn_hadr_backup_is_preferred_replica funcation, but would like to only have to test for a single databases existance in the availability group. If the one database is in the group, only backup the system databases and those that don't participate, otherwise backup everydatabase. This would be the case for both full backups and transaction logs.

View 1 Replies View Related

How To Know Encrypted Password From Database

Nov 20, 2014

I Forgot for my longtime used home expense update application password which has backend sql expressedition database.

i was used the application before 3 years, unfortunately i stopped updating my home expendature to the software.and now i require to login the application but i dont how reset the password in db, i have open database include tables of users profile. and password, but its encrypted.

View 1 Replies View Related

How To Store An Encrypted Password Into A Sql Database?

Apr 20, 2005

Hi everyone,
I am currently reading ASP.NET unleashed and practising few examples.  The following code converts a user's text into a symmetric encryption:
'nd: define keys
Const DESKey As String = "ABCDEFGH"
Const DESIV As String = "HGFEDCBA"
'nd: convert string to byte array
Function convert2ByteArray(ByVal strInput As String) As Byte()
Dim intCounter As Integer
Dim arrChar As Char()
arrChar = strInput.ToCharArray
Dim arrByte(arrChar.Length - 1) As Byte
For intCounter = 0 To arrByte.Length - 1
arrByte(intCounter) = Convert.ToByte(arrChar(intCounter))
Next
Return arrByte
End Function
 
Private Sub btnGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGo.Click
Dim arrDESKey As Byte()
Dim arrDESIV As Byte()
Dim arrInput As Byte()
Dim objFileStream As FileStream
Dim objDES As DESCryptoServiceProvider
Dim objEncryptor As ICryptoTransform
Dim objCryptoStream As CryptoStream
'convert string to bytes
arrDESKey = convert2ByteArray(DESKey)
arrDESIV = convert2ByteArray(DESIV)
arrInput = convert2ByteArray(txtInput.Text)
objDES = New DESCryptoServiceProvider
'pass keys
objEncryptor = objDES.CreateEncryptor(arrDESKey, arrDESIV)
'create to file to save password
objFileStream = New FileStream(MapPath("secret.txt"), FileMode.Create, FileAccess.Write)
'pass in file and keys
objCryptoStream = New CryptoStream(objFileStream, objEncryptor, CryptoStreamMode.Write)
'pass in text
objCryptoStream.Write(arrInput, 0, arrInput.Length)
objCryptoStream.Close()
lblDone.Text = "Done!"
End Sub
 
It works fine.  But, how to i save this encrypted password into a database field instead of a writing it to a file?  Also, could some please tell me how to paste code into this forum?  I tried <code></code> tags but it did not work.
Many thanks,
Kevin

View 9 Replies View Related

SQL 2012 :: SSRS Using Encrypted Database?

Dec 10, 2014

I have an encrypted database in server A. The reporting service is running on server B. I deployed all my reports to Server B. When I run the reports, I got the following message:

The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database.

I googled the problem, some said to backup the key, but when I opened the reporting service configuration tool to backup the encrypted, it said I needed to restore the key first. Some said I need to delete the key, but that defeated the purpose of an encrypted database.

View 0 Replies View Related

Encrypted Database Transfer Problem

Apr 4, 2006

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

View 4 Replies View Related

SQL 2012 :: Decrypting Encrypted Fields From Another Database

May 5, 2015

I am executing a stored procedure in one database (Database1) that pulls data from another database (Database2) that is the back end for a third party application. Some of the fields in that other database are now encrypted. I need to decrypt those fields but since the query is running in a database other than where the data lives (which is also where the symmetric key + cert lives), I am getting the following error: "Cannot find the symmetric key" Below is an example of what I am running in the stored procedure:

OPEN SYMMETRIC KEY [XXXXKey] DECRYPTION
BY CERTIFICATE [XXXX_CERT];
select CONVERT(Varchar(50), DECRYPTBYKEY( <ENCRYPTED FIELD> ))
FROM Database2.dbo.TABLE1
CLOSE SYMMETRIC KEY [XXXXKey];

What do I need to add to Database1 so the stored procedure can decrypt the data it pulls from Database2?

View 5 Replies View Related







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