Encrypt Data In A Stored Procedure

Jul 11, 2006

I am trying to insert data in a table using a stored procedure, but somehow I cannot store the values passed by the stored procedure in the table.

Table has two fields FIRST_NAME, LAST_NAME with varbinary data type(I need to encrypt the data)

My stored procedure is as follows. Please let me know what i am doing wrong!

***************************************************************

ALTER PROCEDURE [dbo].[SP_InsertInfo]
-- Add the parameters for the stored procedure here

@FIRST_NAME varBINARY(100)
,@LAST_NAME varBINARY(100)

AS
OPEN SYMMETRIC KEY key DECRYPTION BY CERTIFICATE cert

BEGIN

SET NOCOUNT ON;

-- Insert statements for procedure here


Insert into [dbo].[INFO] (FIRST_NAME, LAST_NAME)
Values ( encryptbykey( key_guid('key'),'@FIRST_NAME'),
encryptbykey( key_guid('key'),'@LAST_NAME')
)
close SYMMETRIC KEY key

END

**********************************************
EXEC sp_InsertInfo 'larry', 'Smith'

when I run the SP, the data stored in the first_name, last_name fields are @FIRST_NAME', @LAST_NAME' instead of larry, smith respectively.

Thanks

View 4 Replies


ADVERTISEMENT

How To Encrypt The Stored Procedure ?

Feb 17, 2008

Hi,
I would like to know that how can I encrypt a stored procedure, so that it can not be decrypt by me or not by any one ? I have tried using the SQL encrypt,but there is a decrypt command which decrypt the same. I do not want any one to decrypt without a password or encrypt the stored procedure so that it can never be decrypted.
Thanking you,
 Regards..Jay

View 2 Replies View Related

Encrypt A Stored Procedure.

Feb 2, 2004

How might I encrypt a stored procedure in SQL server.

In sybase this is done with SP_HIDETEXT but SQL server doesn't appear to have this.

Is there another way?

Thanks,

View 5 Replies View Related

Encrypt Stored Procedure

May 28, 2008

How to encrypt the Text Of Stored Procedure
Pls Sir Give me Small Example


Yaman

View 1 Replies View Related

How To Encrypt All Existing Stored Procedure?

Aug 18, 2006

I know that we can CREATE PROCEDURE procedure_name WITH ENCRYPTION.

But how about if I want encrypt existing stored procedures?

Which command should I use ?

View 4 Replies View Related

Encrypt Dara Throw A Stored Procedure

Apr 4, 2007

Hi,



I'm trying to use a stored procedure to encrypt data but it dosent work fine, this is how I proceeded and that worked well




Code Snippet

CREATE PROCEDURE [dbo].[UpdateUser]



@CardNumber nvarchar(max),

@UserID int

AS

BEGIN



SET NOCOUNT ON;





DECLARE @SecretData varbinary(max)



OPEN SYMMETRIC KEY MY_SYMMETRIC_KEY

DECRYPTION BY CERTIFICATE [MY_CERTIFICATE]



DECLARE @KeyGuid AS UNIQUEIDENTIFIER

SET @KeyGuid = key_guid( 'MY_SYMMETRIC_KEY')

SET @SecretData = encryptbykey( @KeyGuid, @CardNumber)



UPDATE User

SET

CardNumber=@SecretData

Where UserID=@UserID





CLOSE SYMMETRIC KEY MY_SYMMETRIC_KEY



END





but Now I use my Stored Procedure to encrypt the data, but I'm getting bad data when I decrypt.






Code Snippet

CREATE PROCEDURE [dbo].[UpdateUser]



@CardNumber nvarchar(max),

@UserID int

AS

BEGIN





SET NOCOUNT ON;



DECLARE @SecretData varbinary(max)



exec EncryptData @CardNumber, @SecretData output



UPDATE User

SET

CardNumber=@SecretData



Where UserID=@UserID



END






Code Snippet

CREATE PROCEDURE [EncryptData]

@ClearData varchar(max),

@SecretData varbinary(max) output

WITH EXECUTE AS 'DBO'

AS

BEGIN



OPEN SYMMETRIC KEY MY_SYMMETRIC_KEY

DECRYPTION BY CERTIFICATE [MY_CERTIFICATE]



DECLARE @KeyGuid AS UNIQUEIDENTIFIER

SET @KeyGuid = key_guid( 'MY_SYMMETRIC_KEY')

SET @SecretData = encryptbykey( @KeyGuid, @ClearData)



CLOSE SYMMETRIC KEY My_SYMMETRIC_KEY

END



Any Idea how to fix this issue



Thanks in advance.

View 3 Replies View Related

Transact SQL :: Encrypt Java Code Using With Encryption Clause From Server Stored Procedure Or Function

Nov 3, 2015

How to encrypt the java application code using the 'with encryption' clause from sql server stored procedure or function.

View 3 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

Transact SQL :: How To Encrypt Procedure In The View Itself Using RSA

Oct 27, 2015

How to encrypt the procedure in the view itself using RSA?

View 10 Replies View Related

Encrypt Stored Passwords

Sep 18, 2001

I'm running SQL 70 SP 3 on Nt4.

We store passwords of users of our website. They need to be autenticated and based on that it gives them access to what they are entitled. But its not like NT or server authentication.

This has been setup so that we have a user table and it stores the password. However, it stores it in plain text. Is there any way I can encrypt this field so it is unreadable? Is there a property or a datatype that I can't find? Is there a way to simulate the encryption?

Any ideas or help are appreciated.

Thanks
Kelsey

View 1 Replies View Related

Encrypt All The Stored Proc

Apr 19, 2006

Is there a way to encrypt all the Stored Procedures in a database at a time?

Thx
Venu

View 8 Replies View Related

How To Encrypt And Decrypt Stored Procedures?

May 7, 2006

I encrypt my procedures using with encryption clause, but I do not how to decrypt again.

Is there a command or utility for encrypt and decrypt in Sql 2000? How about Sql 2005?

Thanks

Haydee

View 12 Replies View Related

How To Encrypt The Stored Procedures In SQL 2005 Express

Jan 29, 2007

Dear All,

I am using SQL 2005 Express, and i need to Encrypt all my Stored Procedure while deploying in my Production Server.

Help me out to do.

View 1 Replies View Related

How Can I Assign A Stored Procedure As Cursor's Data Source In AStored Procedure?

Oct 8, 2007

How can I create a Cursor into a Stored Procedure, with another Stored Procedure as data source?

Something like this:

CREATE PROCEDURE TestHardDisk
AS
BEGIN

DECLARE CURSOR HardDisk_Cursor
FOR Exec xp_FixedDrives
-- The cursor needs a SELECT Statement and no accepts an Stored Procedure as Data Source

OPEN CURSOR HardDisk_Cursor


FETCH NEXT FROM HardDisk_Cursor
INTO @Drive, @Space

WHILE @@FETCH_STATUS = 0
BEGIN

...
END
END

View 6 Replies View Related

Encrypt Data

Jan 8, 2007

Hi All,
In SQL 2000, i need to Export Data,so that datashould be encrypted.When i try to import that in any database it should authenticate the user and should get decrypted.IZAT Possible.

Can any one help?

Thanks,
Karthik

View 1 Replies View Related

Encrypt Data

Jan 8, 2007

Hi,

In SQL2000 i need to Encryptdata when I export data using DTS.LikeWise I should when i import data I should authenticate that user and decrypt that.

Can any one help?

Thanks,

Karthik



View 1 Replies View Related

Encrypt Data In Database

Jun 23, 2007

Hi,I have a .net application and i added a code that encrypts data saved in database. However, there is already data in the fields that was entered before this change.I know need to check if the values in those fields are encrypted and if not i need to encrypt them.How can I perform such a check and update the relevant data?I use TrippleDES in .net to encrypt/decrypt the data.Thanks

View 1 Replies View Related

Encrypt Data Issue

Sep 24, 2004

Does SQL Server 2000 provide any data encryption/decryption functionality so that certain fields (e.g. SSN, Age and Salary) will be encrypted before writing into the table and decrypted once loading out of the table?

J827

View 1 Replies View Related

Encrypt/decrypt Data

Nov 16, 2004

We like to secure datas.
Only a few people are autorized to read this information, but today, these informations are readable with a simple query with a query analyzer for exemple.

I'd like to encrypt datas with reversible function in one field of a table

Is there a function able to do this kind of work in SQLServer V7 or 2000 ?

View 1 Replies View Related

Best Approach To Encrypt Data?

Sep 12, 2007

Hi,

I want to encrypt certain data like password, ssn, credit card info etc before saving in database. Also, this encrypted data can be queried using standard SQL statements like:

select * from users where userid=454 and password = 'encrypted data'

The mechanism to encrypt data could be in a .net application. The code that does encryption/decryption should also be protected so that it doesnt work if it falls in wrong hands.

Can anyone suggest what would be the best way to accomplish above?

thanks,
dapi

View 3 Replies View Related

How To Md5 Encrypt Data Using TSQL

Aug 3, 2007

how can i encrypt using md5 for encrypt my data using TSQL and from Enterprise Manager?

View 8 Replies View Related

Getting Data From A Storeed Procedure In A Stored Procedure

Jul 23, 2005

What I am looking to do is use a complicated stored procedure to getdata for me while in another stored procedure.Its like a view, but a view you can't pass parameters to.In essence I would like a sproc that would be like thisCreate Procedure NewSprocASSelect * from MAIN_SPROC 'a','b',.....WHERE .........Or Delcare Table @TEMP@Temp = MAIN_SPROC 'a','b',.....Any ideas how I could return rows of data from a sproc into anothersproc and then run a WHERE clause on that data?ThanksChris Auer

View 4 Replies View Related

Hash/Encrypt CSV To Lookup Repeating Data?

Oct 1, 2007

Hopefully this makes sense, not sure what to even begin researching...

I'm trying to optimize all facets of this process, as it will take over the resources on my server if not done efficiently.

I have CSV files containing INTs that I need to upsert (match to an existing/earlier imported array or create a new record set) millions of times a day. To be clear, this data is a small subset of the actual import, this arrays contents are not the main data of the process, and the value of the entire array is meant to be related to higher level tables.

The contents of the CSV array are 99.9+% repeating, meaning they will very often share the exact same contents as a a previously imported array. A rough guess is there are 20k combinations existing, and less than 1k new per month, and will range from 6 cols x 15 rows to 6 cols x 50 rows.

So current plan is to use a MD5 hash during the (not SQL related) export process to identify the contents of this CSV file, and export only the md5 (32 digit hex) as a lookup to identify the contents. If the SQL import process finds a new (unknown) MD5 it will request the actual contents, otherwise it will simply use the MD5 as a key/id/code for the actual array contents that are already stored.

There's probably a certain terminology I'm not familiar with for this type of thing.. I've never heard of something like this. I realize collision is a threat, but I'm unsure how much I should be worried about it with this type of data (similar size/contents, but a relatively small amount of possibilities). I think up to even 0.1% collision would be acceptable which is probably way more than enough.


Does this sound like a bad idea to anyone? Are there certain hash functions I should use for this type of thing? Anyone have suggestions of where to look next?

Thanks!

View 1 Replies View Related

Encrypt And Decrypt Text Format Data

Oct 19, 2007

Hi,

We have migrated a CRM Database from SQLServer 2000 to SQLServer 2005.



Database contains very sensitive data about customer in text format (Datatype varchar(20)) how can i encrypt the same without any change in the table design.



Regards
Sufian


View 6 Replies View Related

Transact SQL :: Any Way To Encrypt Varbinary Column Data?

Nov 4, 2015

Is there a way to encrypt 'varbinary' column data?

View 9 Replies View Related

How Do I Call A Stored Procedure To Insert Data In SQL Server In SSIS Data Flow Task

Jan 29, 2008



I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks

View 6 Replies View Related

Is There Any Built In Facility To Encrypt A Column Data In SQLSERVER/MSDE

Jun 8, 2005

Hi

View 4 Replies View Related

How To Encrypt My Password Or Sensitive Data Before Storing Them In A Database , Using SQL Server 2005?[urgent Plz Help]

Jan 7, 2007

Hi there ,1. i have a database and i want to encrypt my passwords before storing my records in a database plus i will later on would require to  authenticate my user so again i have to encrypt the string provided by him to compare it with my encrypted password in database below is my code , i dont know how to do it , plz help 2. one thing more i am storing IP addresses of my users as a "varchar" is there a better method to do it , if yes plz help me    try        {            SqlConnection myConnection = new SqlConnection();            myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["projectConnectionString"].ConnectionString;            SqlDataAdapter myAdapter = new SqlDataAdapter("SELECT *From User_Info", myConnection);            SqlCommandBuilder builder = new SqlCommandBuilder(myAdapter);            DataSet myDataset = new DataSet();            myAdapter.Fill(myDataset, "User_Info");            //Adding New Row in User_Info Table               DataRow myRow = myDataset.Tables["User_Info"].NewRow();            myRow["user_name"] = this.user_name.Text;            myRow["password"] = this.password.Text; // shoule be encrypted             //not known till now how to do it                       myRow["name"] = this.name.Text;            myRow["ip_address"] = this.ip_address.Text;                        myDataset.Tables["User_Info"].Rows.Add(myRow);            myAdapter.Update(myDataset, "User_Info");            myConnection.Close();            myConnection.Dispose();        }        catch (Exception ex)        {            this.error.Text = "Error ocurred in Creating User : " + ex.Message;        }  

View 3 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

End Stored Procedure If No Data

Oct 15, 2013

I have written the following stored procedure to export a csv file. I am wanting to put a If statement in here so if view UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_VIEW returns nothing then the procedure does not run.

INSERT INTO UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_TABLE_TEMP
SELECT * FROM UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_VIEW

INSERT INTO UDEF_DISPATCHER_INTERFACE_ORDER_LINE_TABLE_TEMP
SELECT * FROM UDEF_DISPATCHER_INTERFACE_ORDER_LINE_VIEW

DECLARE @BcpHeader AS VARCHAR (2000)

[Code] ....

View 2 Replies View Related

Retrieving Data Using A Stored Procedure

Mar 31, 2007

I have created the following stored procedure and tried to retrieve it's output value in C#, however I am getting exceptions. Can anyone tell me what I am doing wrong? Thanks!  1 ALTER PROCEDURE [dbo].[GetCustomerById]
2
3 @CustId NCHAR(5),
4 @CustomerName NVARCHAR(50) OUTPUT
5
6 AS
7 BEGIN
8
9 SELECT @CustomerName = ContactName
10 FROM Customers
11 WHERE CustomerId = @CustId
12
13 END
14
15 RETURN
16
17
18
19
20
21
22 SqlConnection conn = GetConnection(); //retrieves a new SqlConnection
23 SqlCommand cmd = new SqlCommand();
24 cmd.Connection = conn;
25 cmd.CommandType = CommandType.StoredProcedure;
26 cmd.CommandText = "GetCustomerById";
27
28 SqlParameter paramCustId = new SqlParameter();
29 paramCustId.ParameterName = "@CustId";
30 paramCustId.SqlDbType = SqlDbType.NChar;
31 paramCustId.Direction = ParameterDirection.Input;
32 paramCustId.Value = "ALFKI";
33
34 SqlParameter paramCustomerName = new SqlParameter();
35 paramCustomerName.ParameterName = "@CustomerName";
36 paramCustomerName.SqlDbType = SqlDbType.NVarChar;
37 paramCustomerName.Direction = ParameterDirection.Output;
38
39 cmd.Parameters.Add(paramReturn);
40 cmd.Parameters.Add(paramCustId);
41 cmd.Parameters.Add(paramCustomerName);
42
43 conn.Open();
44 SqlDataReader reader = cmd.ExecuteReader();
45
46 string custName = cmd.Parameters["@CustomerName"].Value.ToString();
  

View 6 Replies View Related

Getting Text Data Into XML Stored Procedure

Jan 16, 2008

Hi,I've got some XML which exists as a text variable in a temp table in SQL Server 2000.I need to pass this XML into sp_xml_preparedocument so I can rebuild a table out of it. But I can't figure out the syntax.If I try doing this:declare @idoc intexec sp_xml_preparedocument @idoc output, (select XmlResult from #cache)I get an error, with or without the brackets round the select statement.The temp table is created using an SP, but I can't call that directly either. This:declare @idoc intexec sp_xml_preparedocument @idoc output, exec Search$GetCache @searchIDAlso throws an error.I can't put it into a
local variable because they can't be of type text. I can't pass it into
the SP somewhere as it's being generated on the fly.How can I get my xml into sp_xml_preparedocument?Cheers,Matt

View 3 Replies View Related

Data Repeating In Stored Procedure

Jan 31, 2008

Can someone tell me why my stored procedure is repeating the Name in the same column?
Here's my stored procedure and output:
select distinct libraryrequest.loanrequestID, titles.title, requestors.fname + ' ' + requestors.lname as [Name],
Cast(DATEPART(m, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.RequestDate) as Varchar(5)) as RequestDate,
Cast(DATEPART(m, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.shipdate) as Varchar(5)) as ShipDate
from LibraryRequest
join requestors on requestors.requestorid=libraryrequest.requestoridjoin titles on titles.titleid = requestors.titleidwhere shipdate is not null 
Output: 
ID      Title                      Name                   Request Date     Ship Date
29     Heads, You Win     Brenda Smith        1/18/2008          1/18/200835     Still More Games    Brenda Smith        1/22/2008          1/22/200851     The Key to..           Brenda Smith  Brenda Smith 1/29/2008 1/29/200852     PASSION...           Brenda Smith  Brenda Smith  1/29/2008 1/29/200853     LEADERSHIP       Brenda Smith   Brenda Smith  1/29/2008  1/29/2008
Going crazy ugh... 
 
 

View 3 Replies View Related







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