2

I have a little side project at my work. My task is to deploy some SQLCLR code into our dev-database (SQL Server 2017) to test it. If the tests are successfull, we want to develop triggers and procedures in SQLCLR and deploy it to our customers. We got a multi-tenant database design. I'm pretty new to SQL Server and SQLCLR, in fact it is my first projects.

My questions

  1. How does the deployment of a SQL Server Database Project work? I bind the dll into the SQL Server on my dev machine. Let's assume it works fine, i push it in source control. From this point on does it deploy it to the different databases from our customers?
  2. To bind it in our dev-machine i assume i need the db_owner/sysadmin rights to create the asymmetric keys for the authentification of the dll. Does the customer also need the rights in their databases to bind dll?

What have i tried

I did two test projects, in the one i created a SQL Server Database Project in in VS17 and in the other i created a simple C# class-library

  1. In the case of the SQL Server Database Project i followed the tutorials Stairway to SQLCLR and get an execution and data provider error:

    • SQL 72045 script error->The error occures on a GO statemant. It's marked with a redline underneath

scrip execution error

  • SQL7214: .net sqlclient data provider: msg 5000, level 16, state 1 ->here i get a red line for a whitespace

data provider error

  1. In the case of the c#-library i can bind the .dll with setting TRUSTWORTHY on in the sql studio (with the proper rights), but i don't want to turn of security settings. Without the proper rights (we don't know the rights of our customers in their DB) i can't create a asymmetric key for the dll in the server for login.

I also read about SQLCLR in the docs of Microsoft and also tried the tutorial SQLQuantumLeap.

EDIT: The error from SQL72045 script error, went from an GO statement to an IF statemant (see picture below). IF error

ConnectioinTypeTest.sql: This scirpt is put together from AssemblySecuritySetup1, AssemblySecuritySetup2 and PreDeploy-SecuritySetup. For the other script see below the ConnectionTypeTest.sql Script. The errors are shown only in the ConnectionTypeTest.sql and not in the parts of the whole script.

/*
Deployment script for db

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "db"
:setvar DefaultFilePrefix "db"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
USE [$(DatabaseName)];


GO
IF (EXISTS(
           SELECT   sc.*
           FROM     sys.configurations sc
           WHERE    sc.[name] = N'clr enabled'
           AND      sc.[value_in_use] = 0
          )
   )
BEGIN
    EXEC sp_configure 'clr enabled', 1;
    RECONFIGURE;
END;
GO

/**********************************************
 * Script:  AssemblySecuritySetup.sql
 * Date:    2016-01-20
 * By:      Solomon Rutzky
 * Of:      Sql Quantum Leap ( http://SqlQuantumLeap.com )
 * 
 * Stairway to SQLCLR - Level 6: Development Tools
 *
 * Stairway to SQLCLR series:
 * http://www.sqlservercentral.com/stairway/105855/
 * 
 **********************************************/


USE [master];
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
GO
--------------------------------------------------------------------------------

DECLARE @ErrorMessage NVARCHAR(4000);



DECLARE @AssemblyName sysname, 
        @AsymmetricKeyName sysname,
        @LoginName sysname, 
        @PublicKeyToken VARBINARY(32),
        @SQL NVARCHAR(MAX);

SET @AssemblyName = N'$StairwayToSQLCLR-TEMPORARY-KeyInfo$';

SET @AsymmetricKeyName = N'StairwayToSQLCLR-06_PermissionKey';
SET @LoginName = N'StairwayToSQLCLR-06_PermissionLogin';

BEGIN TRY
    BEGIN TRAN;

    IF (NOT EXISTS(
                SELECT  *
                FROM        [sys].[assemblies] sa
                WHERE   [sa].[name] = @AssemblyName
            )
        )
    BEGIN
        SET @SQL = N'
        CREATE ASSEMBLY [' + @AssemblyName + N']
        AUTHORIZATION [dbo]

    FROM 0x4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000000000000000000000000400000;
';
        EXEC (@SQL);
    END;

    SET @PublicKeyToken = CONVERT(VARBINARY(32), ASSEMBLYPROPERTY(@AssemblyName, 'PublicKey'));

    IF (NOT EXISTS(
                SELECT  *
                FROM        [sys].[asymmetric_keys] sak
                WHERE   sak.[thumbprint] = @PublicKeyToken
            )
        )
    BEGIN
        SET @SQL = N'CREATE ASYMMETRIC KEY [' + @AsymmetricKeyName + N'] AUTHORIZATION [dbo] FROM ASSEMBLY [' + @AssemblyName + N'];';
        EXEC (@SQL);
    END;

    SET @SQL = N'DROP ASSEMBLY [' + @AssemblyName + N'];';
    EXEC (@SQL);

    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK TRAN;
    END;

    SET @ErrorMessage = ERROR_MESSAGE();
    RAISERROR(@ErrorMessage, 16, 1);
    RETURN; -- exit the script
END CATCH;


-- If the Asymmetric Key exists but the Login does not exist, we need to:
-- 1) Create the Login
-- 2) Grant the appropriate permission
IF (EXISTS(
            SELECT  *
            FROM        [sys].[asymmetric_keys] sak
            WHERE   sak.[thumbprint] = @PublicKeyToken
        )
    ) AND
    (NOT EXISTS(
            SELECT      *
            FROM            [sys].[server_principals] sp
            INNER JOIN  [sys].[asymmetric_keys] sak
                    ON  sak.[sid] = sp.[sid]
            WHERE   sak.[thumbprint] = @PublicKeyToken
        )
    )
BEGIN
    BEGIN TRY
        BEGIN TRAN;

        SET @SQL = N'CREATE LOGIN [' + @LoginName + N'] FROM ASYMMETRIC KEY [' + @AsymmetricKeyName + N'];';
        EXEC (@SQL);

        SET @SQL = N'GRANT EXTERNAL ACCESS ASSEMBLY TO [' + @LoginName + N'];';
        EXEC (@SQL);

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF (@@TRANCOUNT > 0)
        BEGIN
            ROLLBACK TRAN;
        END;

        SET @ErrorMessage = ERROR_MESSAGE();
        RAISERROR(@ErrorMessage, 16, 1);
        RETURN; -- exit the script
    END CATCH;
END;
--------------------------------------------------------------------------------




GO
USE [$(DatabaseName)];
GO

GO
PRINT N'Creating [StairwayToSQLCLR_06_ConnectionTypeTest1]...';


GO
CREATE ASSEMBLY [StairwayToSQLCLR_06_ConnectionTypeTest1]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000... AS N'StairwayToSQLCLR_06_ConnectionTypeTest1.pdb';


GO
PRINT N'Creating [dbo].[StairwayToSQLCLR_ConnectionTest]...';


GO
CREATE FUNCTION [dbo].[StairwayToSQLCLR_ConnectionTest]
(@SqlToExecute NVARCHAR (MAX) NULL, @UseImpersonation BIT NULL)
RETURNS SQL_VARIANT
AS
 EXTERNAL NAME [StairwayToSQLCLR_06_ConnectionTypeTest1].[Testing].[ConnectionTypeTest]


GO
PRINT N'Update complete.';


GO

AssemblySecuritySetup1


USE [master];
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
GO
--------------------------------------------------------------------------------

DECLARE @ErrorMessage NVARCHAR(4000);




DECLARE @AssemblyName sysname,
        @AsymmetricKeyName sysname, 
        @LoginName sysname, 
        @PublicKeyToken VARBINARY(32),
        @SQL NVARCHAR(MAX);

SET @AssemblyName = N'$StairwayToSQLCLR-TEMPORARY-KeyInfo$';

SET @AsymmetricKeyName = N'StairwayToSQLCLR-06_PermissionKey';
SET @LoginName = N'StairwayToSQLCLR-06_PermissionLogin';

BEGIN TRY
    BEGIN TRAN;

    IF (NOT EXISTS(
                SELECT  *
                FROM        [sys].[assemblies] sa
                WHERE   [sa].[name] = @AssemblyName
            )
        )
    BEGIN
        SET @SQL = N'
        CREATE ASSEMBLY [' + @AssemblyName + N']
        AUTHORIZATION [dbo]

AssemblySecuritySetup2

';
        EXEC (@SQL);
    END;

    SET @PublicKeyToken = CONVERT(VARBINARY(32), ASSEMBLYPROPERTY(@AssemblyName, 'PublicKey'));

    IF (NOT EXISTS(
                SELECT  *
                FROM        [sys].[asymmetric_keys] sak
                WHERE   sak.[thumbprint] = @PublicKeyToken
            )
        )
    BEGIN
        SET @SQL = N'CREATE ASYMMETRIC KEY [' + @AsymmetricKeyName + N'] AUTHORIZATION [dbo] FROM ASSEMBLY [' + @AssemblyName + N'];';
        EXEC (@SQL);
    END;

    SET @SQL = N'DROP ASSEMBLY [' + @AssemblyName + N'];';
    EXEC (@SQL);

    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK TRAN;
    END;

    SET @ErrorMessage = ERROR_MESSAGE();
    RAISERROR(@ErrorMessage, 16, 1);
    RETURN; -- exit the script
END CATCH;


-- If the Asymmetric Key exists but the Login does not exist, we need to:
-- 1) Create the Login
-- 2) Grant the appropriate permission
IF (EXISTS(
            SELECT  *
            FROM        [sys].[asymmetric_keys] sak
            WHERE   sak.[thumbprint] = @PublicKeyToken
        )
    ) AND
    (NOT EXISTS(
            SELECT      *
            FROM            [sys].[server_principals] sp
            INNER JOIN  [sys].[asymmetric_keys] sak
                    ON  sak.[sid] = sp.[sid]
            WHERE   sak.[thumbprint] = @PublicKeyToken
        )
    )
BEGIN
    BEGIN TRY
        BEGIN TRAN;

        SET @SQL = N'CREATE LOGIN [' + @LoginName + N'] FROM ASYMMETRIC KEY [' + @AsymmetricKeyName + N'];';
        EXEC (@SQL);

        SET @SQL = N'GRANT EXTERNAL ACCESS ASSEMBLY TO [' + @LoginName + N'];';
        EXEC (@SQL);

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF (@@TRANCOUNT > 0)
        BEGIN
            ROLLBACK TRAN;
        END;

        SET @ErrorMessage = ERROR_MESSAGE();
        RAISERROR(@ErrorMessage, 16, 1);
        RETURN; -- exit the script
    END CATCH;
END;
--------------------------------------------------------------------------------



PreDeploy-SecuritySetup

IF (EXISTS(
           SELECT   sc.*
           FROM     sys.configurations sc
           WHERE    sc.[name] = N'clr enabled'
           AND      sc.[value_in_use] = 0
          )
   )
BEGIN
    EXEC sp_configure 'clr enabled', 1;
    RECONFIGURE;
END;
GO

:r "C:\Users\kzais\source\repos\StairwayToSQLCLR-06_ConnectionTypeTest\AssemblySecuritySetup.sql"

GO
USE [$(DatabaseName)];
GO
  • GO isn't any native SQL command. It is a batch separator in SSMS by default. So, I don't think it should be presented in your scripts. – Denis Rubashkin Apr 3 at 10:34
  • @DenisRubashkin The GO isn't the problem. These scripts are meant to be executed in SSMS, SQLCMD, SSDT...all of them being client tools that can handle GO. There are already GO statements in the SSDT publish script anyway.. – Solomon Rutzky Apr 3 at 20:49
1

I am the author of both of those articles. The second article (the newer one found on https://SqlQuauntumLeap.com/ ) completely replaces the earlier one found on SQL Server Central (the "Stairway to SQLCLR" article). And, since you are using SQL Server 2017, you need to use the newer one.

At the moment I am not sure about the first error related to the GO statement. I might need to see more of the script that comes before that part, or might need more of the error message. Is that error from the completed install script, or from the partial pre-release script? The only thing that matters is the completed script once you have done a build. There are partial scripts that will have syntax errors before the build because it is done in pieces, once the pieces are put together by the build process there won't be any syntax errors.

As far as permissions go, sysadmin is only needed to enable "clr enabled" and maybe to create the login and grant the UNSAFE ASSEMBLY permission. The DB user only needs CREATE ASSEMBLY in order to load the assembly once the rest of it is done. But this script is all inclusive, so it assumes that the login/user executing it has all of the necessary permissions.

  • Sorry for respoding a bit late on your answer. The error occures in the ConnectionTypeTest Project (Starway to SQLCLR code from article). The part where the error with the GO appears, is in the PreDeploy-SecuritySetup. I get the errors only if the projects are build and in the whole install script, in the pieces before it's put together i have no errors shown. I'll edit my question, cause strangly the error went from GO to an IF statemant. – WeedyWookie Apr 5 at 7:56
  • @WeedyWookie What is the error message you are getting. The number doesn't really help nearly as much. Also, since you are on SQL Server 2017, you really do need to use the script from my blog post (SQL Server 2017 and newer), not from the article on SQL Server Central (SQL Server 2016 and prior). – Solomon Rutzky Apr 5 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.