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 RepliesHey yall
I ran into something interesting today and was wondering how one would do this. I have some 3rd party stored procs and one was kicking out a truncate error so I took it upon myself to investigate the stored proc that was kicking out this error. So when I tried viewing the sp, I received an error:*****Encrypted object is not transferable, and script could not be generated.***** and then it brings up a blank editing screen.
First I believe this was a custom error message as it just doesnt seem like the way SQL Server would have presented it.
So how would someone prohibit viewing of a stored proc like this?
Thanks
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.
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?
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.
When I open BIDS it displays a list of "Recent Projects". How can I edit (or delete) items from this list? (Over time the list has acquired a lot of junk est projects that I no longer need).
TIA,
Barkingdog
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?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=497273&SiteID=1
I have gone through the above and, while I can connect to the sql server and activate connections, I can not add a sql database to a project.
Open a project
Right click - add new item
Choose sql database
or
Add existing item
choose existing sql database
results in:
"Connections to SQL server files (*.mdf) require sql server express 2005 to function properly blah blah blah"
Like I said, I can connect to the DB through the server explorer and view table definitions and data. I just can't add one to a solution. I have reinstalled sqlexpress already and am trying to avoid having to reinstall visual studio.
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?
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.
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."
hi,
View 1 Replies View RelatedI 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.
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
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
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
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.
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.
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]
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
I have a CSV File, I am importing this into SQL Server using SSIS package through Flat File source Task.
Few Points about data & its handling
1) inside the Procedure they are dropping the index, then populating the table, then again creating the same index.
2) Data is huge in figure (say, in millions)
My doubt:
which is the best way to import the data
1) Just inserting the data without dropping the Index
2) Drop index, populate table, re-create index (the way they do right now)
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.
I am currently trying to migrate a excel file into SQL 2012 database on an Oracle Virtual Box VM. I am new to SQL and this type of IT in general.
View 3 Replies View Relatedhow to import a text file with a list of NI numbers into a new table with a column to list all the NI numbers? I think I use the Select INTO clause, but not sure how to do this?
View 1 Replies View RelatedIs there a way to namespace groups of stored procs to reduce confusion when using them?
For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.
We are importing xer formats through the wizard to sqlserver database and It takes upto 35-45 mins for each import (single project), any option to reduce the time.Is they any other import options - which can give us faster results.
View 0 Replies View RelatedI try to import packages into integration service catalog, i am getting the master key error .
" Please create a master key in the database or open the master key in the session before performing the operation (error:15581)""
Version is SQl 2012
Hi!
View 36 Replies View RelatedTo 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
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Hi All,Here is my code Dim strCon As String = System.Configuration.ConfigurationSettings.AppSettings("OwnerTrader") con = New SqlConnection(strCon) con.Open() Dim strselect As String = "" Try strselect = "INSERT INTO tbl_CSV(CSV_TIME,CSV_SIZE,CSV_LOCATION,CSV_COUNTRY,CSV_LAT,CSV_LON,CSV_COMMENTS)" strselect = strselect & " SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=C:UploadFiles;Extensions=CSV;','SELECT * FROM TestNew.csv')" cmd = New SqlCommand(strselect, con) cmd.ExecuteNonQuery() con.Close()i have defined connection string in web.config file and my csv file is inside C:UploadFiles with the name TestNew.csv file.Can u please check it out the code?is it correct or not.I am getting this error"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online."Regards
View 1 Replies View RelatedI use ms sql 2000 and i wanted to import a new backup database into a new ms sql 2000 on a different machine but it does not work so i wanted to know if it can be done or i have to do something else thank u.
View 3 Replies View RelatedAs a newbie, I'm having problems importing a copy of my SQLServer2k development database from my laptop (via a CD), to my production SQL Server2k database.
I've tried the DTS Import/Export wizard & also the Copy wizard, but can't seem to see the dialog that prompts & identifies the source path & filename.
I've tried setting up an ODBC DSN for the database, but get the same problem.
Coming from an Access background, I've been used to the copying of *.mdb files, and half expected that level of simplicity with SQLS2K - but clearly, I'm wrong and am missing something, which the on-line help isn't able to clarify for me...
Can anyone please give me any pointers for me to get a copy of my development database along with its stored procedures, from my laptop (not networked), to my desktop server. Both use SQLS2k.
Thanks in advance, Alex