SQL 2017 CLR Functions

A work need started a quest to create CLR functions in C# for use in SQL Server 2017. This is the saga of a proof-of-concept implementation.

Visual Studio Work

In Visual Studio 2019, I created a SQL data project and wrote a simple function using the required SQL data types.

namespace TAMUS.CLR.Sample
{
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString ToUpperCase(SqlString input)
{
if (input == null)
return null;
return new SqlString(input.ToString().ToUpper());
}
}
}

Note that SqlString does not seem to support plain old VARCHAR fields; NVARCHAR is required (seen later).

SQL 2017 has new security around CLR assemblies that make it advantageous to sign custom assemblies. Easy enough in Visual Studio.

Except that I got an “Access denied” error trying to create a PFX certificate. The resolution was to grant the developer (me) Full Control on this folder:

C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys

NOTE: Perhaps lower access might have worked. Worth trying.

Summary of things to do with the VS project:

• Project Settings:  Set the Target SQL version
• Project Settings => Database Settings, Miscellaneous:  Turn the Trustworthy flag on (?)
  • To-do: Determine if this is necessary
• SQLCLR:  Set the Permission level value to EXTERNAL_ACCESS
• Signing:  Sign the assembly with a password-protected PFX file.
  • Generate a single PFX file for the team and use it on all CLR projects.

Except for the permission error noted above, the Visual Studio work was as expected.

SQL Server Work

In SQL, things were more complicated. Note that all work was done using my NT account with the SQL sysadmin permission.

The master Database

The first step is to enable CLR functionality on the SQL server:

use master
go

sp_configure ‘show advanced options’, 1
RECONFIGURE
GO

EXEC sp_configure ‘clr enabled’, 1;
RECONFIGURE;
GO

EXEC sp_configure;
GO

select * from sys.dm_clr_properties;
GO

Asymmetric Keys

The best way to get started with managing CLR assemblies seemed to be to create an asymmetric key using the signed assembly:

USE master;
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘masterpassword’
GO

CREATE ASYMMETRIC KEY TestCLRFuncsSQLKey
FROM EXECUTABLE FILE = ‘C:\Users\moore\source\repos\TestCLRFuncs\TestCLRFuncs\bin\Release\TestCLRFuncs.dll’;
GO

CLOSE MASTER KEY;
GO

Full disclosure: I didn’t discover the master key elements until later.

This should have worked, but I got hung up here on this error:

The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

So was the assembly signed right or not? Long story short, it was. Took a while to figure that out.

The sn tool can help here by telling you if the assembly is signed or not:

sn -v [assembly file]

The real issue was that the SQL Server service did not have permissions to the …\bin\Release folder. I gave the SQL service account Full Control over the project folder and the error was solved.

NOTE: Perhaps lower access might have worked. Worth trying.

NOTE: Because of some secondary experiments with SNK files and another, pre-existing certificate, I granted the permission at the project level, rather than the bin level:

C:\Users\moore\source\repos\TestCLRFuncs\TestCLRFuncs

After adding the file system privileges, the create key permission error was solved.

However, there was a new issue in that the new, dedicated PoC SQL Server did not have a master key (recall previous foreshadowing). There was an auto-created master server key on the SQL server, so it was easy to create the master key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘somepassword’;

After that I was now able to create the asymmetric key with the script above.

The next step was to create a login to associate with the key and to give it unsafe assembly permissions:

CREATE LOGIN TestCLRFuncSQLLogin FROM ASYMMETRIC KEY TestCLRFuncsSQLKey;
GO

GRANT UNSAFE ASSEMBLY TO TestCLRFuncSQLLogin;
GO

Note that the grant of UNSAFE worked for the upcoming “App Database” steps, whereas EXTERNAL ACCESS did not.

Certificates

A co-worker wanted to use an existing code signing certificate instead of creating one via Visual Studio. This did not work for me in Visual Studio 2019 using the PFX he delivered.

The first step with this approach was to stop signing the assembly in Visual Studio.

Signing the Assembly Manually

This step requires the SignTool.exe program from MS, either from the Windows SDK or your Click Once bin folder, if you have that installed). After rebuilding the assembly without signing, use SignTool to sign it with the pre-existing PFX:

“C:\Program Files (x86)\Microsoft SDKs\ClickOnce\SignTool\signtool.exe” sign /f C:\Users\moore\source\repos\TestCLRFuncs\TestCLRFuncs\CodeSigning.pfx /p [password] C:\Users\moore\source\repos\TestCLRFuncs\TestCLRFuncs\bin\Release\TestCLRFuncs.dll

Add the Certificate to SQL Server

The next step was to split the PFX into its certificate and private key for use in SQL Server. This required me to download and install the PVKConverter and MSVC++ runtime from Microsoft:

PVKConverter: https://www.microsoft.com/en-us/download/details.aspx?id=40812

MSVC++: https://www.microsoft.com/en-us/download/details.aspx?id=30679

That done, converting the PFX is done like this:

“C:\Program Files\Microsoft\PVKConverter\PVKConverter.exe” -i C:\Users\moore\source\repos\TestCLRFuncs\TestCLRFuncs\CodeSigning.pfx -o C:\Users\moore\source\repos\TestCLRFuncs\TestCLRFuncs\PVK\CodeSigning2.pvk -d [PFX password] -e [PVK password]

Once this is done, create the certificate in SQL Server. In this case, there were multiple certificates in the PFX and the one to use was #4 (YMMV):

CREATE CERTIFICATE TestCLRFuncsSQLCert
FROM FILE = ‘C:\Users\moore\source\repos\TestCLRFuncs\TestCLRFuncs\PVK\CodeSigning2.pvk_4.cer’
WITH PRIVATE KEY (
FILE = ‘C:\Users\moore\source\repos\TestCLRFuncs\TestCLRFuncs\PVK\CodeSigning2.pvk_4.pvk’,
DECRYPTION BY PASSWORD = ‘[PVK password]’
);
GO

Finally, create the logon in essentially the same way we did with the asymmetric key:

CREATE LOGIN TestCLRFuncSQLLogin FROM CERTIFICATE TestCLRFuncsSQLCert;
GO

The App Database

Regardless of whether the asymmetric key or certificate approach is used, the next step is to switch to the application database and create the actual assembly reference:

USE TestDB;
GO

CREATE ASSEMBLY SQLCLRTest
FROM ‘C:\Users\moore\source\repos\TestCLRFuncs\TestCLRFuncs\bin\Release\TestCLRFuncs.dll’;
GO

Then wrap the CLR function in a UDF. Note the use of the SQL assembly name, the .NET namespace/class names, and the strange calling syntax that omits the parms:

— note that there’s no support for plain old VARCHAR, it seems
CREATE FUNCTION dbo.fncUpperCase(@input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
EXTERNAL NAME SQLCLRTest.[TAMUS.CLR.Sample.UserDefinedFunctions].ToUpperCase
GO

And done. In the end, registering CLR functions with SQL 2017 worked per the docs, but be aware of permission issues.

Good references:

https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/assemblies/creating-an-assembly?view=sql-server-ver15

https://stackoverflow.com/questions/41446819/signing-unsafe-assemblies-with-asymmetric-key

https://coding-examples.com/sql/creating-a-csharp-sql-clr-function/

https://nielsberglund.com/2017/07/02/sql-server-2017-sqlclr-and-permissions/

https://www.sqlshack.com/impact-clr-strict-security-configuration-setting-sql-server-2017/

Other Side Threads Not Ultimately Needed

Simple Signing

When I was having trouble with the PFX creation in Visual Studio I investigated using a simple signing key, created with the SN tool at the VS command line:

sn -k .\TestCLRFuncsSigningKey.snk

This worked fine to sign the assembly in Visual Studio and with creating the key and assembly in SQL as well; however, the PFX approach is recommended with a secure password.

Extract the PK with OpenSSL

c:\openssl\bin\openssl.exe pkcs12 -in C:\Users\moore\source\repos\TestCLRFuncs\TestCLRFuncs\CodeSigning.pfx -nocerts -out C:\Users\moore\source\repos\TestCLRFuncs\TestCLRFuncs\CodeSigning.pvk -nodes

Enter Import Password:[paste password]

Remove passphrase from  PK

c:\openssl\bin\openssl.exe rsa -in C:\Users\moore\source\repos\TestCLRFuncs\TestCLRFuncs\CodeSigning.pvk -out C:\Users\moore\source\repos\TestCLRFuncs\TestCLRFuncs\CodeSigning_NO_PW.pvk

References:

https://wiki.cac.washington.edu/display/infra/Extracting+Certificate+and+Private+Key+Files+from+a+.pfx+File

https://stackoverflow.com/questions/31869552/how-to-install-signtool-exe-for-windows-10