So if I have a nested function, then I will have what I call the parent function (the one called in the queries) and the child function (the one that is called by the parent function). The code for the parent function is
- CREATE FUNCTION [dbo].[EncryptString](@plainText [nvarchar](max), @PW [nvarchar](max))
-
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
-
AS
-
EXTERNAL NAME [VB_Encryption_Test].[VB_Encryption_Test.AESManagedProc].[Encrypt]
-
GO
The code for the child function is
- CREATE FUNCTION [dbo].[PW]()
-
RETURNS nvarchar(max)
-
AS
-
BEGIN
-
DECLARE @PWD nvarchar(max)
-
DECLARE @ID smallint
-
SET @ID = 2 --Change for individual database
-
SELECT @PWD = PW
-
FROM tempdb.dbo.PasswordTable
-
WHERE PWID = @ID;
-
RETURN @PWD
-
END;
So instead of the EncryptString function requiring the PW argument, I just want the function to automatically call the PW function.
@CK The code that the assembly links to encrypts and decrypts the data. I know that SQL Server has the ability to encrypt and decrypt data, but my problem is that I am linking to the data from MS Access and I can't open a key from Access nor can a function change the state of the database. So to make the data easily modified through Access, I import a view instead of the actual table. The view decrypts the data automatically. Then I have Insert and Update triggers to encrypt the data that I change and then pass the encrypted data on to the table. If you have another solution I would love to hear it, but I have searched for months trying to figure out a way to encrypt/decrypt data in SQL Server in a manner that allows for easy integration with Access.
2,965
