Transact-SQL Reference


SQL Server 2008 Books Online (October 2009)
SignByAsymKey (Transact-SQL)

Signs plaintext with an asymmetric key

Topic link icon Transact-SQL Syntax Conventions

Syntax
SignByAsymKey( Asym_Key_ID , @plaintext [ , 'password' ] )
Arguments
Asym_Key_ID

Is the ID of an asymmetric key in the current database. Asym_Key_ID is int.

@plaintext

Is a variable of type nvarchar, char, varchar, or nchar containing data that will be signed with the asymmetric key.

password

Is the password with which the private key is protected. password is nvarchar(128).

Return Types

varbinary with a maximum size of 8,000 bytes.

Remarks

Requires CONTROL permission on the asymmetric key.
Examples

The following example creates a table, SignedData04, in which to store plaintext and its signature. It next inserts a record in the table, signed with asymmetric key PrimeKey, which is first decrypted with password 'pGFD4bb925DGvbd2439587y'. 

-- Create a table in which to store the data
CREATE TABLE [SignedData04]( Description nvarchar(max), Data nvarchar(max), DataSignature varbinary(8000) );
GO
-- Store data together with its signature
DECLARE @clear_text_data nvarchar(max);
set @clear_text_data = N'Important numbers 2, 3, 5, 7, 11, 13, 17, 
      19, 23, 29, 31, 37, 41, 43, 47, 53, 59, 61, 67, 71, 73, 79,
      83, 89, 97';
INSERT INTO [SignedData04] 
    VALUES( N'data encrypted by asymmetric key ''PrimeKey''',
    @clear_text_data, SignByAsymKey( AsymKey_Id( 'PrimeKey' ),
    @clear_text_data, N'pGFD4bb925DGvbd2439587y' ));
GO
See Also

Reference

ASYMKEY_ID (Transact-SQL)
VerifySignedByAsymKey (Transact-SQL)
CREATE ASYMMETRIC KEY (Transact-SQL)
ALTER ASYMMETRIC KEY (Transact-SQL)
DROP ASYMMETRIC KEY (Transact-SQL)

Other Resources

Encryption Hierarchy

Help and Information

Getting SQL Server 2008 Assistance
Tags :


Community Content

Mike C_1
The example is misleading

This function will only sign the first 8,000 bytes of data in the plaintext. The function chops off everything after the 8,000th byte of data, ignoring it. The example indicates that the function will accept and sign an nvarchar(max) data type value, but again anything after the 8,000th byte is discarded. Below is a more accurate example:

-- Create a table in which to store the data
CREATE TABLE [SignedData04]( Description nvarchar(4000), Data nvarchar(4000), DataSignature varbinary(8000) );
GO
-- Store data together with its signature
DECLARE @clear_text_data nvarchar(4000);
set @clear_text_data = N'Important numbers 2, 3, 5, 7, 11, 13, 17, 
      19, 23, 29, 31, 37, 41, 43, 47, 53, 59, 61, 67, 71, 73, 79,
      83, 89, 97';
INSERT INTO [SignedData04] 
    VALUES( N'data encrypted by asymmetric key ''PrimeKey''',
    @clear_text_data, SignByAsymKey( AsymKey_Id( 'PrimeKey' ),
    @clear_text_data, N'pGFD4bb925DGvbd2439587y' ));
GO
Tags : contentbug

Page view tracker