Opening Symmetric Keys In Stored Procedures
Apr 26, 2007
I can't seem to use symmetric keys in stored procedures. I am trying to decrypt a column in my stored precedure but the values are all NULL. First I tried a symmetric key that required a password but I kept getting simple syntax errors when I tried to send the password to the stored precedure as a parameter. Now I am trying a symmetric key that uses a certificate.
I also tried using WITH EXECUTE AS SELF and WITH EXECUTE AS CALLER but that made no difference.
View 1 Replies
ADVERTISEMENT
Mar 28, 2006
Hi,
I want to create a symmetric key that will be encrypted by certificate key. Can u guide me which algorithm is best out of the following:
DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, AES_256.
I tried using AES_128, AES_192, AES_256 but it says 'the algorithm specified for the key are not available in this installation of Windows.'
Pls tell me which else algorithm is best to use and pls specify why.
Thanks
Gaurav
View 5 Replies
View Related
Feb 2, 2007
I'm trying to develop a procedure for re-encrypting data with a different symmetric key, but I need the new key to use the old key name. I want to do this without leaving the data in clear text at any point. I would think the process would work like this:
1.Create a new key
2.Decrypt data w/old key and encrypt with new key.
3.Drop old key.
4.Rename new key using the old key name.
However, I can't find that there's a way to rename a symmetric key. Is this correct? If that is the case, I believe I have to do an additional round of encryption:
1.Create a temp key
2.Decrypt data w/old key and encrypt with temp key.
3.Drop old key.
4.Create a new key with the old key name.
5.Decrypt data w/temp key and encrypt with new key.
6.Drop temp key
Thanks for any insight.
View 4 Replies
View Related
Nov 9, 2007
i've getting ready to implement encryption on a rather large database. I'd read that if performance is of utmost concert, you should use symmetric keys. I want to encrypt those keys by asymmetric keys. My code is working, but i'm just not sure if there is a quicker way? do you have to open and close the key each time you select/update/insert in a stored procedure that references an encrypted column, or is there a way to just modify the code by adding the encryptbykey/decryptbykey functions?
has anyone implemented encryption on columns in large tables? any suggestions for me?
Thanks,
Pete
here's my code to create the keys:
create asymmetric key ASK_Auto_Encrypt
with algorithm = RSA_512;
create symmetric key SK_AE
with algorithm = TRIPLE_DES
encryption by asymmetric key ASK_Auto_Encrypt;
here's my code to test this:
create table encryption_test (test varchar(50));
open symmetric key SK_AE
decryption by asymmetric key ASK_Auto_Encrypt;
insert into encryption_test
select encryptbykey(key_guid('SK_AE'),'test');
select convert(varchar(max),decryptbykey(test)) from encryption_test;
close symmetric key SK_AE;
View 14 Replies
View Related
Apr 24, 2007
I'm having some issues restoring a backup of database that uses native encryption onto another server. I know there are a couple of articles on this but I seem to be missing something. Any help would be greatly appreciated.
Current Server Windows 2000
Destination Server Windows 2003
The original key setup for the current server was achieved by something like this:
CREATE SYMMETRIC KEY HR01 WITH algorithm=DES encryption BY password = 'HRpassword'
Running the command select * from sys.symmetric_keys on the current server I get the following:
name
principal_id
symmetric_key_id
key_length
key_algorithm
algorithm_desc
create_date
modify_date
key_guid
HR01
1
256
56
D
DES
2006-11-22 16:36:01.883
2006-11-22 16:36:01.883
BBD80500-338F-47D7-B336-85D46E00F2F0
So I restored the database onto the new server and ran this script:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
I don't have a master key... so I go back to the original server and ran this script and did another backup:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password'
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
Running select * from sys.symmetric_keys on the current server now looks like this:
name
principal_id
symmetric_key_id
key_length
key_algorithm
algorithm_desc
create_date
modify_date
key_guid
##MS_DatabaseMasterKey##
1
101
128
D3
TRIPLE_DES
2007-04-23 16:03:09.183
2007-04-23 17:02:46.630
1CB0D800-0173-4A1E-B841-362B454E60AC
HR01
1
256
56
D
DES
2006-11-22 16:36:01.883
2006-11-22 16:36:01.883
BBD80500-338F-47D7-B336-85D46E00F2F0
I then restored the new backup onto the 2003 server and ran this script:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
The alter script now runs fine but I get the error message: The decryption key is incorrect when trying to open the HR01 key
View 1 Replies
View Related
Apr 9, 2007
I've played with various configurations of the MS SQL Server encryption functionality, and come across an embarrassingly easy question that I cannot seem to resolve. How do I retrieve the actual symmetric and asymmetric keys out of the database?
I'd like to explore the possibility of off-loading the encryption/decryption work from the database server to a load-balanced pool of servers. For this model to work the pool would need access to the keys. The symmetric keys currently are generated with the command...
create symmetric key EncryptionKey211
with algorithm = AES_256
ENCRYPTION BY certificate CreditCardCert
Am I missing something obvious here?
View 4 Replies
View Related
Sep 26, 2007
Is there a limit on number of symmetric keys/asymmetric keys/certificates that can be stored in a database?
Is there any effect on performance of the SQL Server if I have too many (few hundreds) symmetric keys in the database?
thanks
View 1 Replies
View Related
May 29, 2006
Hi There
We have been playing around with encryption in 2005. I cannot find a BOL topic that discusses dropping encryption objects such as keys.
We do the followign steps:
Create master key with password, then we create a certificate using the master key, we then create a symmetric key using this certificate and encrypt data columns.
But what i find worrying is that you can then drop the symmetric key , there are no warnings that you have objects dependant on this key for decryption.
Once you have dropped the key you cannot decrypt the data anymore?
Also the key defults the expiration date to 1 year.
WHat happens after 1 year when you have encrypted data and an expired key, or someone drops the key ? How can you ever decrypt the data after that ?
You can backup master keys nd certificates but not symmetric keys?
It seems to be that youc an very easily orphan encrypted data by the loss of the symmetric key for whatever reason, is this correct ?
Thanx
View 6 Replies
View Related
Feb 14, 2007
I have a question about the storage of symmetric keys in SQL Server 2005 due to the fact that I have read two conflicting statements on this.
In Laurentiu's blog located at http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx, in regards to preventing symmetric key loss he makes the statement that "...Because the keys are stored in the database, they will be saved with the database....".
But in the white paper Improving Data Security by Using SQL Server 2005, which is located at http://www.microsoft.com/technet/itshowcase/content/sqldatsec.mspx, in regards to symmetric keys the statement is made "...Note: The symmetric key is not stored in the database. Only the encrypted values of the symmetric key are stored in the database. Therefore, users who can access the database cannot decrypt the data without first decrypting the symmetric key....".
So I am just wondering which statement is correct, are symmetric keys stored in the database or not?
Thanks!
Ginny
View 1 Replies
View Related
Apr 17, 2015
why my script is not allowing me to DECRYPTBYKEY once I restore my DB from PRODUCTION BACKUP....
-- SET Staging to Single User Mode to be able to RESTORE DB---
-- STEP 1 (Works No Problems Here)
USE master;
GO
ALTER DATABASE Staging
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE Staging
FROM DISK = 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupMyDBRestore.bak' ;
GO
-- STEP 2 - USE ONLY IF THE ABOVE IS UNSUCCESSFUL ||| FAILURE ****** RESTORE RUN THE FOLLOWING SCRIPT -----
--If the above is successful the DB sets itself back to MULTI_USER
--ALTER DATABASE Staging
--SET MULTI_USER;
--GO
--ALTER DATABASE Staging
--SET READ_WRITE
--GO
-- STEP 3 (Works No Problems Here)
--------------- @@@@@@@@@@@@@@ IMPORTANT UNCOMMIT AND RUN @@@@@@@@@@@@@@@@@@@@@@@@@ MANUAL STEPS ----------------------
-- RBD - Recreate Security ID'S AND PERMISSIONS FOR Stored Procedure EXECUTE RIGHTS, because PASSWORDS are different on lower
-- environments
--USE [Staging]
--GO
--/****** Object: User [WebUser] Script Date: 4/13/2015 11:15:51 AM ******/
--DROP USER [WebUser]
[code]....
View 1 Replies
View Related
Jan 30, 2007
Hello. I have a problem that spans VB.net, SQL Server and SSIS but is rooted in the need to encrypt column data in SQL Server.
I would like to encrypt data that I am bringing into SQL Server in the Data transformation script component of an SSIS package. I have achieved this but I can't decrypt the data because the keys don't match. I would like to use symmetric key encryption but I don't see how to get the symmetric key that I created in SQL Server available to the VB.net script component in SSIS.
Please advise me if my approach is correct and what steps I need to take.
View 5 Replies
View Related
Jul 29, 2015
We had a server give out on us completely. All that is left is a back up of the SSRS database. We have been able to get a new SSRS environment working but the symmetric key doesn't seem to work. Reports will run if we delete all sensitive data through the Config manager and reenter the connection strings into the datasource but if we can get the symmetric key saved in the ReportingServices database to work correctly it will save us a ton of effort. Can we make use of the symmetric key saved in the ReportServer.dbo.Keys table?
View 2 Replies
View Related
Jun 25, 2015
I am trying to implement the column encryption on one of the tables, have used the below link as the reference and got stuck at the last step.
[URL] ....
I have completed the following steps so far.
- CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘myStrongPassword’
- CREATE CERTIFICATE MyCertificateName
WITH SUBJECT = 'A label for this certificate'
- CREATE SYMMETRIC KEY MySymmetricKeyName WITH
IDENTITY_VALUE = 'a fairly secure name',
ALGORITHM = AES_256,
[Code] .....
Example by using the function
EXEC OpenKeys
-- Encrypting
SELECT Encrypt(myColumn) FROM myTable
-- Decrypting
SELECT Decrypt(myColumn) FROM myTable
When I ran the last command :
-- Decrypting
SELECT Decrypt(myColumn) FROM myTable
I get the following error :
Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query.
Where will I use the convert function, in decrypt function or in select statement?
View 9 Replies
View Related
Jan 22, 2008
Say i have some symmetric keys encrypted by other symmetric keys ,
is there some way to find the relationship of these key?
I know i can find the certificate or asymmetric key through sys.key_encryptions catalog view by "thumbprint" column,
but the thumbprint column is null when the key was encrypted by a symmetric key.
any suggestions?
thanks in advance
View 1 Replies
View Related
Jul 23, 2005
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies
View Related
Sep 30, 2006
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
View 1 Replies
View Related
Jun 30, 2006
I created a package using the ImportExport wizard and saved it to the local Sql server. Under Management Studio I can see the package, can run in, import it, export it BUT how am I supposed to edit (modify the thing) it?
TIA,
barkingdog
View 9 Replies
View Related
Jul 20, 2005
In SQL how can a cursor be opened to iterate the result set returnedfrom a stored proc-Rahul SoodJoin Bytes!
View 1 Replies
View Related
Jun 6, 2006
I am able to connect to Integration Services in MSSMS. However, when I try to expand and click on MSDB under Stored Packages, I get a Login Timeout expired error. I get this error both locally, and remotely. Error message suggested "does not allow remote connections", but I checked Surface Area Configuration, and remote connection is already enabled. Moreover, I get the same error even when connecting locally.
any ideas?
View 1 Replies
View Related
Nov 6, 2007
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
View 16 Replies
View Related
Jul 23, 2005
I have a table which has 10 columns which make up the secondary key. 1or more of these columns can be set but the remaining columns in thesecondary key will be null. For example :id k1 k2 k3 k4 k5 k6 k7 k8 k9 k10 data-------------------------------------------------0 1 1 - - - - - - - - test01 1 1 1 - - - - - - - test12 1 1 2 - - - - - - - test23 1 1 3 - - - - - - - test34 1 1 3 1 - - - - - - test45 1 2 1 - - - - - - - test56 1 2 2 - - - - - - - test6Each row represents a node in a tree structure, the secondary keycolumns point to which node in the tree. The above example has onenode in tree branch 1, three nodes in tree branch 1-1, one node inbranch 1-1-3 and two nodes in branch 1-2.My question is how can I write a single stored procedure to returnonly the nodes in a given tree branch without needing logic based onlooking for NULL parameters. I have written the stored procedurebelow. If I want to retrieve all nodes in branch 1-1 (ie; test1, test2and test3) then I want be able to call :execute "mysp Test" 1, 1But this only returns a single record, test0. The reason for this isobvious if you look at the stored procedure. What I really need is wayof sayingexecute "mysp Test" 1, 1, NOT NULLso that it returns all records in branch 1-1 where Key3 is NOT NULLbut all subsequent keys are null, ie; Key4-Key10 are NULL. The storedprocedure must work with any number of secondary keys though, so Icould use it to call :execute "mysp Test" 1, 1, 3, NOT NULLwhich would retrieve test4.Can anybody help me out here?Thanks for reading.Here's the stored procedure as it stands :CREATE PROCEDURE [dbo].[mysp Test]( @key1 smallint,@key2 smallint=NULL,@key3 smallint=NULL,@key4 smallint=NULL,@key5 smallint=NULL,@key6 smallint=NULL,@key7 smallint=NULL,@key8 smallint=NULL,@key9 smallint=NULL,@key10 smallint=NULL ) ASselect * from TreePositionTestwherekey1=@key1 andkey2=@key2 andkey3=@key3 andkey4=@key4 andkey5=@key5 andkey6=@key6 andkey7=@key7 andkey8=@key8 andkey9=@key9 andkey10=@key10GO
View 3 Replies
View Related
Apr 11, 2006
Hello again,
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
class_title
VARCHAR(50)
NOT NULL,
class_grade
SMALLINT
NOT NULL
DEFAULT 6,
class_tardies
SMALLINT
NOT NULL
DEFAULT 0,
class_absences
SMALLINT
NOT NULL
DEFAULT 0,
CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
grade_id
INT
NOT NULL,
CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),
CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
Thank you for your assistance.
View 1 Replies
View Related
Jul 16, 2014
what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:
[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------
The foreign key above is as:
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
View 4 Replies
View Related
Apr 17, 2007
I have a database column that stores a comma delimited list of foreignkeys. Would someone show me how to do a join using the values from alist stored within a record?For example, a record in tbl_cds.genre_id might have a value of "2,5, 6" corresponding to genre_ids 2 , 5 and 6. I want to jointbl_cds.genre_id to tbl_genre.genre_id using the values in that datafield.It seems I need a loop like this:SELECT * FROM tbl_cdsWHEREBegin Looptbl_cds.genre_id[i] = tbl_genre.genre_idEnd Loop.Would someone give me the correct syntax?Is there an alternative method that would create less overhead?Sorry for such a novice post.
View 2 Replies
View Related
May 16, 2008
Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.
Also how I script default and other constraints of a table?
View 2 Replies
View Related
Apr 29, 2008
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'
View 1 Replies
View Related
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
May 13, 2008
Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
View 5 Replies
View Related
Apr 7, 2006
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
View 24 Replies
View Related
Apr 23, 2008
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
View 1 Replies
View Related
Mar 26, 2008
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
View 9 Replies
View Related
Jul 15, 2002
Can somebody explain to me how to best do inserts where you have primary keys and foreign keys.l'm battling.
Is there an article on primary keys/Pk ?
View 1 Replies
View Related
Nov 22, 2007
Hello!I have a table A with fields id,startdate and other fields. id and startdateare in the primary key.In the table B I want to introduce a Foreign key to field id of table A.Is this possible? If yes, which kind of key I have to build in table A?Thx in advance,Fritz
View 6 Replies
View Related
