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


ADVERTISEMENT

SQL 2012 :: Delete Encrypted Key In SSRS But Now Report Cannot Connect To Datasource

Dec 10, 2014

I decided to delete the encryption key.After that when I go to the reporting page, the report cannot connect to the datasource.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'xxxxx'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

What do I suppose to do?

View 0 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

SQL 2012 :: Importing Encrypted PROCS Into Database Projects

Jan 19, 2015

I have inherited a database and looking to upgrade it from 2008 to 2014. I have imported the database into db projects but it has flagged warning s straight away because one of the previous contractors has created a bunch of stored procs with encryption.

View 0 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 2012 :: Encrypted Records In All Columns

Nov 6, 2014

I have idea on SMK, DMK and symmetric and asymmetric keys. I have also idea on TDE. But Is there any way to encrypt all the records of all the columns of a table in a database? actually I need to encrypt the database. Someone .... thinks that when someone will write select query he will get the encrypted records. As per as I am concerned it is not possible. I can encrypt the specific column using symmetric or other keys...

Is there any software or any tool which will provide encrypted records of database?

View 1 Replies View Related

SQL 2012 :: Enabling SSL Encrypted Connections Without Using Force Encryption Option

Apr 1, 2015

I have a customer who is looking to encrypt their applications connection to the back-end SQL server. I've gotten a valid certificate for the server from a trusted CA, and it's installed on the server (shows up in Personal certificates in the Certificate MMC when selecting the computer) and I can choose it in the drop down on the certificates tab of the SQL Network protocols properties in Configuration Manager.

Now, my question: If I select the certificate but do NOT enable the "Force Encryption" option, the client will be able to request encryption on their end, and the connection will then be encrypted. If the client (other applications) do NOT request encryption, they will still be able to connect, but their connection will be un-encrypted. URL....

Lastly, I could've swore I had found some information indicating that some methods of establishing a connection to SQL can fail when Force Encryption is enabled, as the method doesn't support SSL encryption.

View 1 Replies View Related

SQL 2012 :: Encrypted Data In Project Manifest Failed To Load

Sep 8, 2015

An inherited SSIS (2012) solution displays the following messages when I try opening it in VS 2012:

"One or more projects in the solution were not loaded correctly."

Then another message displays

"The encrypted data in the project manifest failed to load. The project manifest is corrupted or the project was encrypted by another user."

View 8 Replies View Related

Reporting Services :: Interface Code Compatibility Between Custom Delivery Extension Of SSRS 2008R2 And SSRS 2012?

Sep 3, 2015

Currently we are using Custom Delivery Extension for SSRS 2008R2 We are planning to move it SSRS2012

My Question is: Whether we can use the same Code used for SSRS2008R2 to SSRS2012?

if not what code changes we should do?

View 3 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

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

Backup Database With Encrypted Column Data

Jan 25, 2007

Hi,
I have database with encrypted column data in my customer server. Sometime, I might need to backup their database back to office for troubleshooting.

How could I backup/restore master key, symmetric and asymmetric key created for my database?

Thank you

View 6 Replies View Related

SQL 2012 :: TDE Encrypted Databases On 2 Different Server To Be Restored On Single Dev Server

Apr 29, 2015

have a Prod Server A having TDE enabled on 2 of those databases. I have a Prod Server B having TDE enabled on 3 of those databases. Now I have to create a single Dev server Server C for all the above 5 databases residing on the two servers. So how can I restore all the 5 database backup files on server C.

Does it mean that I need to copy the certificates and Keys from both the Prod server to this Dev Box and then restore the backup files. Once done, I can enable the encryption ON on those 5 database on Dev box or is there any different approach.Also how will tempdb behave in this scenario.

View 5 Replies View Related

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 View Related

SQL Security :: Enable Encrypted Connections To Database Engine?

Nov 2, 2015

The option:

SQL Server Configuration Manager>SQL Server Network Configuration>Protocol for SQL_xxx (right click)>Properties, we can see two Tabs:

Tab 1: Flags
Tab 2: Certificate

If I set the value of Hide Instance= Yes, does "Force Enctyption", need to be set YES as well? 

Or Force Enctyption has to be enabled in order to hide instance?

What is the recommend settings?

[URL]

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 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

Reporting Services :: 2012 SSRS Reporting Configuration (SCCM 2012)

May 26, 2015

We have SCCM 2012 primary site and Remote SQL 2012 server. Due to hardening and password reset we are facing reporting issue.

while we Open the SRS report in SQL server and try to edit the Report (Report Builder) we are getting following error due to which we are unable to configure Reporting Service point in the SCCM 2012 server. We created the New Reporting server database still we are getting the below error.

View 14 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 2012 :: Log Shipping On SSRS?

Nov 19, 2014

In an SSRS database install along with a log shipping configuration towards a failover server, do we need to log ship the ReportServerTempDB DB also necessarily ?

View 2 Replies View Related

SQL 2012 :: Dataset Relation In SSRS

Sep 24, 2014

Is there any way to find the dependency between two datasets in ssrs report. I want to delete one of dataset but need to find if I delete that it will work or any relation with the other dataset.

View 1 Replies View Related

SQL 2012 :: SSRS Authentication Through A Proxy

Dec 19, 2014

I have found endless articles online and tried a few different formats of the web.config file but i can not get SSRS authenticating through the proxy.

</runtime>
<system.net>
<defaultProxy enabled="true" useDefaultCredentials="true">
<proxy bypassonlocal="true" proxyaddress="http://PROXY:PORT"/>
</defaultProxy>
</system.net>
</configuration>

Is there a way to pass credentials in so we can specify which account to authenticate with?

View 0 Replies View Related

SQL 2012 :: SSRS Report Is Not Refreshing?

May 12, 2015

I have created an SSRS report, but the data does not refresh. I am using the SSRS Report Buidler to create reports and when I run the query in the dataset, the results return as expected. However, when I run the report itself, the dataset appears old.

View 6 Replies View Related

SQL 2012 :: SSRS Run Selective Pages

Jun 26, 2015

Say I have a report with 3 pages, each page has a different dataset and takes some processing time.

is it possible to give the user the option to just run the first page say if thats all they need?

Reduce the processing time of the report to only that that is necessary?

View 2 Replies View Related

SQL 2012 :: SSRS Report Builder

Oct 27, 2015

While trying to configure data source and data set on Report Builder 3.0 I created a table using table wizard. After I run I get the following error message:

"The report server has encountered a configuration error. logon failed for the unattended execution account. (rsServerConfigurationError)"

I checked the SQL Server Configuration Manager and saw the following error when i click on "SQL Server Services" on the left panel."error message: "The Server threw an exception. [0x80010105]"

View 0 Replies View Related

SQL 2012 :: Calling SSRS Reports Using Query?

Mar 3, 2014

I have created testreport.rdl using SQL Server 2012 SSRS. How to execute/call this through SQL query analyser, so that this will be executed and display output.

View 2 Replies View Related

SQL 2012 :: Connect To SAP From SSRS And Execute Reports?

Apr 14, 2014

can i connect to SAP from SSRS and execute reports?

View 1 Replies View Related

SQL 2012 :: SSRS Report Using New HierarchyID Datatype

Apr 22, 2014

I am using the HierarchyID datatype for my requirement to manage the hierarchy structure. I found it very useful in maintaining the structure in the table with the levels.

I am looking to develop an SSRS report to generate this tree structure. I couldn't find anything relevant todo this using HierarchyID.

I found Parent child hierarchy in SSRS using recursive hierarchy method which was not using the HierarchyID datatype.

View 0 Replies View Related

SQL 2012 :: SSRS Report Deployment To Web Service?

Jun 12, 2014

I created two reports in SSRS and have configured both the web service and report manager URLs in report services configuration manager.

whenever I try to open either one of the two reports from the manager or service I get the following error msg.

"An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.

(rsReportServerDatabaseError)

Invalid object name 'DataSets'. Could not use view or function 'ExtendedDataSets' because of binding errors."

I get a similar msg when I try to deploy the reports from VS 2010. Only the first 2 sentences are returned.

I'm mystified as to why DataSets is an invalid object name. It shows as the TargetDatasetFolder name WebsiteReports Property Page. I've looked around for the error source and can not find where this error is stemming from.

View 0 Replies View Related

SQL Server 2012 :: Creating SSRS Add On Controls?

Sep 5, 2014

I have an area of a report that id like to use on other reports, its just a few images with some data on top.

how id save it as a control so I could use it as an add on?

View 2 Replies View Related

SQL 2012 :: Connecting To Readable Secondary With SSRS

Dec 21, 2014

I have been working with a BI colleague to access the readable secondary through SSRS. For some reason it keeps complaining that ApplicationIntent is not recognized keyword. I am starting to think it's something to do with the driver for SSRS.

View 1 Replies View Related

SQL 2012 :: How To Add A Variance Column Into SSRS Report

May 13, 2015

Below is a picture of my report format. It's the yellow column in trying to calculate which is basically a subtraction between the 7th Column (which is based on the date selected by the user in the parameter) and column 6 which is the day before.

View 1 Replies View Related







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