Msg 6522, Level 16 warning during execution of clr stored procedure

Question

I would like to create SQL Server CLR stored procedure for inserting some rows in a table in SQL Server 2012.

Here is my c# code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void InsertingRows ()
    {
        // Put your code here
        Random rnd = new Random();

        List<int> listtelnumber = new List<int>(new int[] { 1525407, 5423986, 1245398, 32657891, 123658974, 7896534, 12354698 });
        List<string> listfirstname = new List<string>(new string[] { "Babak", "Carolin", "Martin", "Marie", "Susane", "Michail", "Ramona", "Ulf", "Dirk", "Sebastian" });
        List<string> listlastname = new List<string>(new string[] { "Bastan", "Krause", "Rosner", "Gartenmeister", "Rentsch", "Benn", "Kycik", "Leuoth", "Kamkar", "Kolaee" });
        List<string> listadres = new List<string>(new string[] { "Deutschlan Chemnitz Sonnenstraße 59", "",
            "Deutschland Chemnitz Arthur-Strobel straße 124", " Deutschland Chemnitz Brückenstraße 3",
            "Iran Shiraz Chamran Blvd, Niayesh straße Nr.155", "",
            "Deutschland Berlin Charlotenburg Pudbulesky Alleee 52", "United State of America Washington DC. Farbod Alle",
            "" });

            using (SqlConnection conn = new SqlConnection("Data Source=WIN2012SERVER02;Initial Catalog=test;Persist Security Info=True;User ID=di_test;Password=di_test"))
            {
                SqlCommand insertcommand = new SqlCommand();
                SqlParameter firstname = new SqlParameter("@fname", SqlDbType.VarChar);
                SqlParameter lastname = new SqlParameter("@lname", SqlDbType.VarChar);
                SqlParameter tel = new SqlParameter("@tel", SqlDbType.Int);
                SqlParameter adres = new SqlParameter("@adres", SqlDbType.NVarChar);
                conn.Open();
            for (int i = 0; i < 10000; i++)
            {
                int tn = rnd.Next(0, 6);
                int fn = rnd.Next(0, 9);
                int ln = rnd.Next(0, 9);
                int an = rnd.Next(0, 9);

                firstname.Value = listfirstname[fn];
                lastname.Value = listlastname[ln];
                tel.Value = listtelnumber[tn];
                adres.Value = listadres[an];

                insertcommand.Parameters.Add(firstname);
                insertcommand.Parameters.Add(lastname);
                insertcommand.Parameters.Add(tel);
                insertcommand.Parameters.Add(adres);

                insertcommand.CommandText = "INSERT dbo.Unsprstb(Firstname,Lastname,Tel,adress) VALUES(@fname,@lname,@tel,@adres)";
                insertcommand.Connection = conn;

                insertcommand.ExecuteNonQuery();

            }
            conn.Close();
        }
    }
}

I can successfully build, deploy and publish my code in SQL Server, but if I run this CLR stored procedure in SQL Server, I see this message:

Msg 6522, Level 16, State 1, procedure InsertingRows,Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'InsertingRows':
System.Security.SecurityException: Error request for the permission of type "System.Data.SqlClient.SqlClientPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089".
System.Security.SecurityException:
bei System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
bei System.Security.PermissionSet.Demand()
bei System.Data.Common.DbConnectionOptions.DemandPermission()
bei System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
bei System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
bei System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
bei System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
bei System.Data.SqlClient.SqlConnection.Open()
bei StoredProcedures.InsertingRows()

How can I solve this problem?

Answer 1

There are several issues going on in this code that need to be addressed:

1. Regarding the stated question, when you get a System.Security.SecurityException error, that refers to the code trying to reach outside of the database, something that is not allowed in a SAFE assembly. How you fix this depends on what you are trying to accomplish.

   • If you are trying to access the file system, read from the registry, get an environment variable, access the network for a non-SQL Server connection (e.g. http, ftp), etc, then the assembly needs a PERMISSION_SET of EXTERNAL_ACCESS. In order to set your assembly to anything other than SAFE, you need to either:
     • Create a Certificate or Asymmetric Key based on the same key that you used to sign your assembly (i.e. give it a strong name), create a Login based on that Certificate or Asymmetric Key, and then grant the EXTERNAL ACCESS ASSEMBLY permission to that Login. This method is greatly preferred over the other method, which is:
     • Set the database containing the assembly to TRUSTWORTHY ON. This method should only ever be used as a last resort if it is not possible to sign the assembly. Or for quick testing purposes. Setting a database to TRUSTWORTHY ON opens your instance up to potential security threats and should be avoided, even if quicker / easier than the other method.

   • If you are trying to access the SQL Server instance that you are already logged into, then you have the option of using the in-process connection of Context Connection = true; which can be done in a SAFE assembly. This is what @Marc suggested in his answer. While there are definitely benefits to using this type of connection, and while the Context Connection was the appropriate choice in this particular scenario, it is overly-simplistic and incorrect to state that you should always use this type of connection. Let's look at the positive and negative aspects of the Context Connection:

     • Positives:
       • Can be done in a SAFE assembly.
       • Very low, if any, connection overhead as it is not an additional connection.
       • Is part of the current session so any SQL you execute has access to session-based items such as local temporary tables and CONTEXT_INFO.

     • Negatives:

       • Cannot be used if Impersonation has been enabled.
       • Can only connect to the current SQL Server instance.
       • When used in Functions (Scalar and Table-Valued) it has all of the same restrictions that T-SQL functions have (e.g. no side-effecting operations are allowed) except you can execute read-only stored procedures.
       • Table-Valued Functions are not allowed to stream their results back if they read a result set.

       All of these "negatives" are allowed when using a regular / external connection, even if it is to the same instance you are executing this code from.

2. If you are connecting to the instance that you are executing this code from and using an external / regular connection, then no need to specify the Server name or even use localhost. The preferred syntax is Server = (local) which uses Shared Memory whereas the others might sometimes use TCP/IP which is not as efficient.

3. Unless you have a very specific reason for doing so, don't use Persist Security Info=True;

4. It is a good practice to Dispose() of your SqlCommand

5. It is more efficient to call the insertcommand.Parameters.Add() just before the for loop, and then inside of the loop, simply set the value via firstname.Value =, which you are already doing, so just move the insertcommand.Parameters.Add() lines to just before the for line.

6. tel / @tel / listtelnumber are INT instead of VARCHAR / string. Telephone numbers, just like zip-codes and Social Security Numbers (SSNs), are not numbers, even if they appear to be. INT cannot store leading 0s or something like ex. to signify an "extension".

7. All of that being said, even if all of the above is corrected, there is still a huge problem with this code that should be addressed: this is a rather simplistic operation to perform in straight T-SQL, and doing this in SQLCLR is over-complicated, harder and more costly to maintain, and much slower. This code is performing 10,000 separate transactions whereas it could so easily be done as a single set-based query (i.e. one transaction). You could wrap your for loop in a transaction which would speed it up, but it will still always be slower than the set-based T-SQL approach since it still needs to issue 10,000 separate INSERT statements. You can easily randomize in T-SQL by using either NEWID() or CRYPT_GEN_RANDOM which was introduced in SQL Server 2008. (please see the UPDATE section below)

If you want to learn more about SQLCLR, please check out the series I am writing for SQL Server Central: Stairway to SQLCLR (free registration required).

---

UPDATE

Here is a pure T-SQL method of generating this random data, using the values from the Question. It is easy to add new values to any of the 4 table variables (to increase the number of possible combinations) as the query dynamically adjusts the randomization range to fit whatever data is in each table variable (i.e. rows 1 - n).

DECLARE @TelNumber TABLE (TelNumberID INT NOT NULL IDENTITY(1, 1),
                          Num VARCHAR(30) NOT NULL);
INSERT INTO @TelNumber (Num) VALUES ('1525407'), ('5423986'), ('1245398'), ('32657891'),
                                    ('123658974'), ('7896534'), ('12354698');

DECLARE @FirstName TABLE (FirstNameID INT NOT NULL IDENTITY(1, 1),
                          Name NVARCHAR(30) NOT NULL);
INSERT INTO @FirstName (Name) VALUES ('Babak'), ('Carolin'), ('Martin'), ('Marie'),
                  ('Susane'), ('Michail'), ('Ramona'), ('Ulf'), ('Dirk'), ('Sebastian');

DECLARE @LastName TABLE (LastNameID INT NOT NULL IDENTITY(1, 1),
                         Name NVARCHAR(30) NOT NULL);
INSERT INTO @LastName (Name) VALUES ('Bastan'), ('Krause'), ('Rosner'),
                  ('Gartenmeister'), ('Rentsch'), ('Benn'), ('Kycik'), ('Leuoth'),
                  ('Kamkar'), ('Kolaee');

DECLARE @Address TABLE (AddressID INT NOT NULL IDENTITY(1, 1),
                        Addr NVARCHAR(100) NOT NULL);
INSERT INTO @Address (Addr) VALUES ('Deutschlan Chemnitz Sonnenstraße 59'), (''),
  ('Deutschland Chemnitz Arthur-Strobel straße 124'),
  ('Deutschland Chemnitz Brückenstraße 3'),
  ('Iran Shiraz Chamran Blvd, Niayesh straße Nr.155'), (''),
  ('Deutschland Berlin Charlotenburg Pudbulesky Alleee 52'),
  ('United State of America Washington DC. Farbod Alle'), ('');

DECLARE @RowsToInsert INT = 10000;

;WITH rowcounts AS
(
  SELECT (SELECT COUNT(*) FROM @TelNumber) AS [TelNumberRows],
         (SELECT COUNT(*) FROM @FirstName) AS [FirstNameRows],
         (SELECT COUNT(*) FROM @LastName) AS [LastNameRows],
         (SELECT COUNT(*) FROM @Address) AS [AddressRows]
), nums AS
(
  SELECT TOP (@RowsToInsert)
         (CRYPT_GEN_RANDOM(1) % rc.TelNumberRows) + 1 AS [RandomTelNumberID],
         (CRYPT_GEN_RANDOM(1) % rc.FirstNameRows) + 1 AS [RandomFirstNameID],
         (CRYPT_GEN_RANDOM(1) % rc.LastNameRows) + 1 AS [RandomLastNameID],
         (CRYPT_GEN_RANDOM(1) % rc.AddressRows) + 1 AS [RandomAddressID]
  FROM   rowcounts rc
  CROSS JOIN msdb.sys.all_columns sac1
  CROSS JOIN msdb.sys.all_columns sac2
)
-- INSERT dbo.Unsprstb(Firstname, Lastname, Tel, Address)
SELECT fn.Name, ln.Name, tn.Num, ad.Addr
FROM   @FirstName fn
FULL JOIN nums
        ON nums.RandomFirstNameID = fn.FirstNameID
FULL JOIN @LastName ln
        ON ln.LastNameID = nums.RandomLastNameID
FULL JOIN @TelNumber tn
        ON tn.TelNumberID = nums.RandomTelNumberID
FULL JOIN @Address ad
        ON ad.AddressID = nums.RandomAddressID;

Notes:

• The FULL JOINs are needed instead of INNER JOINs to get the entire @RowsToInsert amount of rows.
• Duplicate rows are possible due to the very nature of this randomization AND not filtering them out by using DISTINCT. However, DISTINCT cannot be used with the given sample data in the question since the number of elements in each array / table variable provide for only 6300 unique combinations and the requested number of rows to generate is 10,000. If more values are added to the table variables such that the total possible unique combinations rises above the requested number of rows, then either the DISTINCT keyword can be added to the nums CTE, or the query can be restructured to simply CROSS JOIN all of the table variable, include a ROW_COUNT() field, and grab the TOP(n) using ORDER BY NEWID().
• The INSERT is commented out so it is easier to see that the query above produces the desired result. Just uncomment the INSERT to have the query do the actual DML operation.

Answer 2

Inside your SQL CLR C# code, you should not build a connection with explicit server, database name and credentials -instead, use the "context" connnectino:

using (SqlConnection conn = new SqlConnection("context connection=true"))
{
    // do your stuff here...
}