0

So on SQL Server 2008+ there lives this function:

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-asymmetric-key-transact-sql?view=sql-server-2017

CREATE ASYMMETRIC KEY Asym_Key_Name   
   [ AUTHORIZATION database_principal_name ]  
   [ FROM <Asym_Key_Source> ]  
   [ WITH <key_option> ] 
   [ ENCRYPTION BY <encrypting_mechanism> ] 
   [ ; ]

I want to use a parameter to supply the arguments for Asym_Key_Name and ENCRYPTION BY PASSWORD so that it is not hard coded. Therein lies the problem. How do I do it?

This works (hard coded, not useful to me):

CREATE PROCEDURE [dbo].[asi_spCryptomodServer_NewKeyPair]
    @KeyName nvarchar(128),
    @KeyPassword nvarchar(128)
AS
BEGIN
  CREATE ASYMMETRIC KEY blah
  WITH ALGORITHM = RSA_2048   
  ENCRYPTION BY PASSWORD = '123P@ssword';
END
GO

This is about what I want but SQL Server doesn't like it:

CREATE PROCEDURE [dbo].[asi_spCryptomodServer_NewKeyPair]
    @KeyName nvarchar(128),
    @KeyPassword nvarchar(128)
AS
BEGIN
  CREATE ASYMMETRIC KEY @KeyName
  WITH ALGORITHM = RSA_2048   
  ENCRYPTION BY PASSWORD = @KeyPassword;
END
GO

The latter returns:

Msg 102, Level 15, State 1, Procedure asi_spCryptomodServer_NewKeyPair, Line 11 [Batch Start Line 4] Incorrect syntax near '@KeyName'. Msg 319, Level 15, State 1, Procedure asi_spCryptomodServer_NewKeyPair, Line 12 [Batch Start Line 4] Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

^How do I fix the logic so I can pass in Asym_Key_Name and the password in a parameter?

===BASED ON COMMENTS===

I went down the path of Dynamic SQL as a solution. So the next question is, how do I prevent SQL injection with the following code:

CREATE PROCEDURE [dbo].[asi_spCryptomodServer_NewKeyPair]
    @KeyName nvarchar(128),
    @KeyPassword nvarchar(128)
AS
BEGIN
BEGIN TRY
  DECLARE @KeyPasswordPost nvarchar(128)
  SET @KeyPasswordPost = 'N''' + @KeyPassword + ''''
  DECLARE @KEYSTATEMENT As nvarchar(1024)
  Set @KEYSTATEMENT = 'CREATE ASYMMETRIC KEY ' + @KeyName
  Set @KEYSTATEMENT = @KEYSTATEMENT + ' WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = ' + @KeyPasswordPost + ';'
  EXECUTE sp_Executesql @KEYSTATEMENT
END TRY
BEGIN CATCH
  RAISERROR (15600,-1,-1, 'asi_spCryptomodServer_NewKeyPair');  
END CATCH
END
GO
Timothy John Laird
  • 1,101
  • 2
  • 13
  • 24
  • 2
    You can use dynamic SQL, but be aware that that's inherently unsafe if your connection to SQL Server isn't also encrypted -- the password will just trundle across the line as plaintext. – Jeroen Mostert Jul 09 '18 at 15:16
  • When I created a symmetric key, I too had to use dynamic SQL. I assume creating an asymmetric key will have the same limitation. Here's a link that describes ways to cleanse the SQL to prevent SQL injection: https://stackoverflow.com/questions/4102387/how-to-cleanse-dynamic-sql-in-sql-server-prevent-sql-injection – James L. Jul 09 '18 at 16:40

0 Answers0