Сообщение 6522, уровень 16, предупреждение во время выполнения хранимой процедуры clr

Я хотел бы создать хранимую процедуру SQL Server CLR для вставки некоторых строк в таблицу в SQL Server 2012.

Вот мой код C#:

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();
        }
    }
}

Я могу успешно создавать, развертывать и публиковать свой код в SQL Server, но если я запускаю эту хранимую процедуру CLR в SQL Server, я вижу следующее сообщение:

Сообщение 6522, уровень 16, состояние 1, процедура InsertingRows, строка 0
Ошибка.NET Framework произошла во время выполнения пользовательской подпрограммы или агрегата 'InsertingRows':
System.Security.SecurityException: ошибка запроса на разрешение типа "System.Data.SqlClient.SqlClientPermission, System.Data, версия =4.0.0.0, культура = нейтральная, PublicKeyToken = b77a5c561934e089".
System.Security.SecurityException:
bei System.Security.CodeAccessSecurityEngine.Check (Требование объекта, StackCrawlMark& ​​stackMark, логическое значение 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 externalConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
bei System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 повтор)
bei System.Data.SqlClient.SqlConnection.TryOpen (TaskCompletionSource`1 повтор)
bei System.Data.SqlClient.SqlConnection.Open ()
bei StoredProcedures.InsertingRows ()

Как я могу решить эту проблему?

Источник

2 ответа

Решение

В этом коде происходит несколько проблем, которые необходимо решить:

  1. Что касается поставленного вопроса, когда вы получаете ошибку System.Security.SecurityException, это относится к коду, пытающемуся выйти за пределы базы данных, что недопустимо в SAFE сборка. Как вы это исправите, зависит от того, чего вы пытаетесь достичь.

    • Если вы пытаетесь получить доступ к файловой системе, прочитать из реестра, получить переменную среды, получить доступ к сети для соединения, отличного от SQL Server (например, http, ftp) и т. Д., То сборка требует PERMISSION_SET из EXTERNAL_ACCESS, Для того, чтобы установить вашу сборку на что-либо, кроме SAFEВам нужно либо:
      • Создайте сертификат или асимметричный ключ на основе того же ключа, который вы использовали для подписи вашей сборки (т. Е. Дайте ему строгое имя), создайте логин на основе этого сертификата или асимметричного ключа, а затем предоставьте EXTERNAL ACCESS ASSEMBLY разрешение на этот логин. Этот метод значительно предпочтительнее другого метода, который:
      • Установите базу данных, содержащую сборку, в TRUSTWORTHY ON, Этот метод следует использовать только в качестве крайней меры, если невозможно подписать сборку. Или для быстрого тестирования. Настройка базы данных на TRUSTWORTHY ON открывает ваш экземпляр для потенциальных угроз безопасности, и его следует избегать, даже если он работает быстрее / проще, чем другой метод.

    • Если вы пытаетесь получить доступ к экземпляру SQL Server, в который вы уже вошли, у вас есть возможность использовать внутрипроцессное соединение: Context Connection = true; что может быть сделано в SAFE сборка. Это то, что @Mark предложил в своем ответе. Хотя использование этого типа соединения определенно имеет преимущества, и хотя контекстное соединение было подходящим выбором в данном конкретном сценарии, слишком упрощенно и неверно утверждать, что вы всегда должны использовать этот тип соединения. Давайте посмотрим на положительные и отрицательные аспекты Context Connection:

      • Положительных:
        • Может быть сделано в SAFE сборка.
        • Очень низкие, если таковые имеются, накладные расходы на соединение, поскольку это не дополнительное соединение.
        • Является частью текущего сеанса, поэтому любой выполняемый вами SQL имеет доступ к элементам на основе сеанса, таким как локальные временные таблицы и CONTEXT_INFO,

      • Отрицательных:

        • Не может использоваться, если олицетворение было включено.
        • Можно подключиться только к текущему экземпляру SQL Server.
        • При использовании в функциях (Scalar и Table-Valued) он имеет все те же ограничения, что и функции T-SQL (например, не допускаются побочные операции), за исключением того, что вы можете выполнять хранимые процедуры только для чтения.
        • Табличным функциям не разрешается передавать свои результаты обратно, если они читают набор результатов.

        Все эти "негативы" разрешены при использовании обычного / внешнего соединения, даже если это тот же экземпляр, с которого вы выполняете этот код.

  2. Если вы подключаетесь к экземпляру, с которого вы выполняете этот код, и используете внешнее / обычное соединение, то нет необходимости указывать имя сервера или даже использовать localhost, Предпочтительный синтаксис Server = (local) который использует общую память, тогда как другие могут иногда использовать TCP/IP, который не так эффективен.

  3. Если у вас нет очень конкретной причины для этого, не используйте Persist Security Info=True;

  4. Это хорошая практика для Dispose() вашей SqlCommand

  5. Более эффективно назвать insertcommand.Parameters.Add() как раз перед for цикл, а затем внутри цикла, просто установите значение с помощью firstname.Value =, что вы уже делаете, так что просто переместите insertcommand.Parameters.Add() линии как раз перед for линия.

  6. tel / @tel / listtelnumber являются INT вместо VARCHAR / string, Телефонные номера, такие как почтовые индексы и номера социального страхования (SSN), не являются номерами, даже если они кажутся. INT не может хранить ведущие 0с или что-то вроде ex. чтобы обозначить "расширение".

  7. При этом, несмотря на то, что все вышеперечисленное исправлено, все еще существует огромная проблема с этим кодом, которую необходимо решить: это довольно упрощенная операция для выполнения в прямом T-SQL, и выполнение этого в SQLCLR закончено - сложнее, сложнее и дороже в обслуживании, и намного медленнее. Этот код выполняет 10000 отдельных транзакций, в то время как его можно легко выполнить как один запрос на основе набора (т. Е. Одну транзакцию). Вы могли бы обернуть for Цикл в транзакции, который ускорил бы ее, но он все равно всегда будет медленнее, чем подход T-SQL на основе множеств, поскольку ему по-прежнему необходимо выдавать 10000 отдельных INSERT заявления. Вы можете легко рандомизировать в T-SQL, используя либо NEWID() или CRYPT_GEN_RANDOM, который был представлен в SQL Server 2008. (см. раздел ОБНОВЛЕНИЕ ниже)

Если вы хотите узнать больше о SQLCLR, ознакомьтесь с серией, которую я пишу для SQL Server Central: Лестница в SQLCLR (требуется бесплатная регистрация).

ОБНОВИТЬ

Вот чистый метод T-SQL для генерации этих случайных данных с использованием значений из Вопроса. Легко добавить новые значения к любой из 4 табличных переменных (чтобы увеличить количество возможных комбинаций), так как запрос динамически корректирует диапазон рандомизации, чтобы соответствовать любым данным в каждой табличной переменной (то есть строкам 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;

Заметки:

  • FULL JOINнужны вместо INNER JOINс, чтобы получить весь @RowsToInsert количество строк.
  • Двойные строки возможны из-за самой природы этой рандомизации И не отфильтровывают их с помощью DISTINCT, Тем не мение, DISTINCT не может использоваться с данными данного примера в вопросе, так как количество элементов в каждой переменной массива / таблицы обеспечивает только 6300 уникальных комбинаций, а запрошенное число строк для генерации составляет 10000. Если к табличным переменным добавлено больше значений, так что общее количество возможных уникальных комбинаций превысит запрошенное количество строк, то либо DISTINCT Ключевое слово может быть добавлено к nums CTE, или запрос может быть реструктурирован, чтобы просто CROSS JOIN все переменные таблицы, включают в себя ROW_COUNT() поле, и захватить TOP(n) с помощью ORDER BY NEWID(),
  • INSERT закомментировано, так что легче увидеть, что запрос выше дает желаемый результат. Просто раскомментируйте INSERT чтобы запрос выполнял фактическую операцию DML.
Источник

Внутри кода SQL CLR C# вы не должны устанавливать соединение с явным сервером, именем базы данных и учетными данными - вместо этого используйте connnectino "context":

using (SqlConnection conn = new SqlConnection("context connection=true"))
{
    // do your stuff here...
}
Источник
Другие вопросы по тегам