CLR System.Security.SecurityException

verwendetes Datenbanksystem: <SQL SERVER 2008 R2>

Hallo liebe Community!

Ich erstelle in C# ein Assembly für den SQL Server 2008 R2.
Nun möchte ich aber, dass meine Assembly auf die Datenbank zugreift und eine Abfrage ausführt.
Das Ergenis soll dann in meiner SQL Funktion ausgegeben werden.

Was habe ich bisher getan:
Um die Assembly einzubinden, habe ich Anhand meines Skripts die SQL Server-Oberflächenkonfiguration eingeschaltet und zwar so:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'clr enable', 1
GO
RECONFIGURE
GO

Dies hat auch funktioniert.

Dann habe ich mit VS 2010 ein "SQL CLR-Datenbankprojekt" erstellt.
Am Anfang wird man nach der DB Connection gefragt (da kann man nichts falsch eingeben...)

Hier mein Code:

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


public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString SearchID_User(int ID_CR, String Tab)
    {
        //Berechtigung in der benutzerdefinierten Assembly Assert
        System.Data.SqlClient.SqlClientPermission pSql = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
        pSql.Add("Data Source={SQL Server};Server=./;Database=CIM_RIMIS;Integrated Security=True", "", KeyRestrictionBehavior.AllowOnly);
        pSql.Assert();


        String SqlString;
        String Ergebnis = "Hallo";
        SqlConnection DBConn;
        SqlCommand cmd;
        SqlDataReader DataReader;

        SqlString = "SELECT MIN(Eintrag_ID_User) AS ID_User " +
                            " FROM " + Tab +
                            " WHERE (ID_CR = " + ID_CR + ") AND (Bin_AktuellerDS = 1)";


        DBConn = new System.Data.SqlClient.SqlConnection("Data Source={SQL Server};Server=./;Database=CIM_RIMIS;Integrated Security=SSPI;");
        DBConn.Open();
        cmd = new System.Data.SqlClient.SqlCommand(SqlString, DBConn);
        DataReader = cmd.ExecuteReader();

        while (DataReader.Read())
            Ergebnis = DataReader["ID_User"].ToString();

        DataReader.Close();
        DBConn.Close();

        return new SqlString(Ergebnis);
    }
};

Der Code wird dann unter Erstellen/"Projekt" bereitstellen als Assembly für den SQL Server bereitgestellt.
Dies kann man mit dem Skript

Select * from sys.assemblies

überprüfen.

Nach dem alles bereitgestellt wurde und ich meine Funktion (Skript) ausführen möchte kommt dann der tolle Fehler:> Fehlermeldung:

Meldung 6522, Ebene 16, Status 2, Zeile 11
.NET Framework-Fehler beim Ausführen der benutzerdefinierten Routine oder des benutzerdefinierten Aggregats 'SearchID_User':
System.Security.SecurityException: Fehler bei der Anforderung des Berechtigungstyps "System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089".
System.Security.SecurityException:
bei System.Security.CodeAccessSecurityEngine.CheckNReturnSO(PermissionToken permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 unrestrictedOverride, Int32 create)
bei System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission cap, StackCrawlMark& stackMark)
bei System.Security.CodeAccessPermission.Assert()
bei UserDefinedFunctions.SearchID_User(Int32 ID_CR, String Tab)

hier mein SQL Skript:

USE [CIM_RIMIS]
GO
/****** Object:  UserDefinedFunction [dbo].[SearchID_User]    Script Date: 08/31/2011 09:19:15 ******/
ALTER FUNCTION [dbo].[SearchID_User](@ID_CR [int], @Tab [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SQLDB_CLR_CIM_RIMIS].[UserDefinedFunctions].[SearchID_User]

begin

declare @m_ID_CR [int]
declare @m_Tab [nvarchar](4000)
declare @m_Count [int]
declare @m_ID_User [nvarchar](4000)

set @m_ID_CR =  (Select Top 1 [ID_CR] From [CIM_RIMIS].[dbo].[V_ID_CR_Letzte_Aenderung_3]) --Test für Daten
set @m_Tab = (Select Top 1 [Tab] FROM [CIM_RIMIS].[dbo].[V_ID_CR_Letzte_Aenderung_3]) --Test für Daten

Select [dbo].[SearchID_User](@m_ID_CR, @m_Tab) as ID_User

end

Frage: Kann man mit CLR überhaupt eine Abfrage auf die Datenbank machen, so wie ich es mir oben vorgestellt habe?
Ist es überhaupt richtig was ich da mache, oder funktioniert das ganz anderst mit der Abfrage?
Ich würde mich über eure Hilfe, Tipps und Anregungen freuen.

Vielen Dank schon mal!

Gruß
Nesso

Hallo Nesso

Der korrekte Weg um über SQLCLR auf die aktuelle Datenbank zuzugreifen ist "Context Connection" über den Connection-String anzugeben.

SqlConnection cn = new SqlConnection("Context Connection=true");

Ansonsten würde ich dir empfehlen dir mal folgenden Artikel durchzulesen:
[Artikelserie] SQL: Parameter von Befehlen

Grüße
Flo

Hallo Flo,

danke für den Tipp und Hinweis.

Ich habe jetzt meine Connection nun so geändert:

DBConn = new System.Data.SqlClient.SqlConnection("Context Connection=true");
        DBConn.Open();
        cmd = new System.Data.SqlClient.SqlCommand(SqlString, DBConn);

        DataReader = cmd.ExecuteReader();

        while (DataReader.Read())
            Ergebnis = DataReader["ID_User"].ToString();

        DataReader.Close();
        DBConn.Close();

        return new SqlString(Ergebnis);
    }

Habe ich das so richtig verstanden?
Weil der Fehler bestehen bleibt, da her die Frage.

Sorry muss blöd fragen, weil ich sowas zum erstenmal mache...

Grüße
Nesso

Versuch mal die Assembly auf Unsafe zu stellen. Sollte zwar auf den ersten Blick für deinen Code nicht nötig sein, aber könnte helfen.

@Feuerfalke:
Du hast scheinbar den sinn von Unsafe nicht verstanden.

@Nesso:
Da der SqlServer unter einem anderen Account läuft, musst du dem auch per caspol die entsprechenden berechtigungen geben.

@FZelle,

danke für den Hinweis!
Habe nun weiter recherchiert und bin auch an so Sachen gestoßen wie "Strong Name" etc.

Hast du mir vielleicht ein Beispiel oder eine Erklärung etc. wie ich das zu bewerkstelligen habe?

Grüße
Nesso

Hallo Leute,

habe das Problem gelöst.

Der Fehler lag hier:

[Microsoft.SqlServer.Server.SqlFunction]

Ersetzt man dies durch:

[SqlFunction(DataAccess = DataAccessKind.Read)]

Funktioniert das ganze ganz gut 😃

hier noch ein Link zum Thema CLR Skalarfunktionen

Für die Nachwelt, hier der gesamte Code:

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

public partial class UserDefinedFunctions
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static int F_Seac(Int32 p_ID_CR, String p_Tab)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {

            String SQLString;
            SqlCommand cmd;
            
            SQLString = "SELECT MIN(Eintrag_ID_User) AS ID_User " +
                                " FROM " + p_Tab +
                                " WHERE (ID_CR = " + p_ID_CR + ") AND (Bin_AktuellerDS = 1)";

            conn.Open();
            cmd = new SqlCommand(SQLString, conn);
            cmd.ExecuteScalar();

            return (int)cmd.ExecuteScalar();
 
        }     
    }
};

Für mich kann das Thema geschlossen werden,
danke an Alle, für die Tipps und Anregungen.

Grüße,
Nesso

Nur so am Rande: Ich hoffe mal stark, dass Du hier nur eine Art Proof-of-Concept gezeigt hast und der Code nicht irgendwo im Einsatz ist?
Denn zum einen lässt sich das komplett ohne Verwendung der CLR (also mit reinem T-SQL) lösen* und ausserdem öffnest Du hier Tür und Tor für SQL-Injections!

Bart Simpson

*Wobei ich unabhängig davon der Meinung bin dass 99% der Anwendungsfälle von dynamischem SQL einfach nur von schlechtem Datenbank-Design zeugen.

Hallo,

nein, der Code ist nicht im Einsatz und danke für den Hinweis.

Ich dachte, es ist ein gutes Beispiel für eine CLR um, ein dynamischen SQL Statement zu erzeugen.
Weil ein Datenbankprofie meinte mal zu mir, mann kann keine Variable in einer FROM-Klausel einfügen/ ausführen etc...?

Da ich ziemlich frisch auf dem Gebiet bin, ist mir über T-SQL keine Lösung eingefallen. Mich würde es aber interessieren wie Deine Lösung aussehen könnte.

Na klar, gehört jetzt nicht hier rein aber würde mich über eine Antwort oder PN von Dir freuen.

Grüße,
Nesso

Hallo Nesso

Natürlich kann man auch in T-SQL auch dynamisches SQL erzeugen:

DECLARE @id INT = OBJECT_ID('INFORMATION_SCHEMA.TABLES');
DECLARE @tableName VARCHAR(250) = 'all_objects'
DECLARE @schemaName VARCHAR(250) = 'sys';


-- avoid sql injection
SELECT
   @tableName = QUOTENAME(@tableName)
   ,@schemaName = QUOTENAME(@schemaName);

-- build dynamic SQL
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = N'SELECT * FROM ' + @schemaName + '.' + @tableName + ' WHERE object_id = @idIn';

EXECUTE sp_executesql @sql, N'@idIn INT', @idIn = @id;

Allerdings ist die Frage immer ob man's wirklich braucht. In den meisten Fällen hat man eher ein Design-Problem oder noch nicht die richtige Lösung gefunden.

In wie vielen Tabellen gibt's die Spalte "ID_CR" denn? Warum musst du in mehreren Tabellen nach einer ID suchen?

Grüße
Flo

Hallo Florian,

vielen Dank für dein Beispiel! Find ich klassen wenn man mehrer/bessere Wege gezeigt bekommt.

Die ID_CR kommt in meinen Beispiel in 18 Tabellen vor, habe die DB so bekommen...

Mit dieser ID wird dann die "UserID" ermittellt, an hand dessen kann man in einer Appliaktion genau sagen, wer gerade wo eine Änderung vorgenommen hat.

Allerdingst musste ich auf das "sp_exceutesql" verzichten, weil ich es auf einer View weiter verwenden musste...

Grüße,
Nesso

Wenn's kein (Firmen-) Geheimnis ist, würde mich die DB-Struktur mal interessieren - bei 18 Tabellen wäre es zwar unschön, aber sicher immer noch ohne dynamisches SQL zu bewerkstelligen.
Wenn möglich, lass doch mal ein Script erzeugen um die Tabellen / Views zu erzeugen und Poste es hier. Ich denke da findet sich schnell jemand, der Dir das Ganze auch "anders" erklären kann 😉

Bart Simpson

Nun ja,

meine Testdatenbank ist vom Kunde, die ich zum spielen bekommen habe^^
(auch wenn es ein alter Stand ist...)

Und sie zu verändern, für diesen Beispiel ist schwer, weil es doch sehr komplex ist und man auch verstehen muss, was das ganze eigentlich soll und warum die Struktur und so weiter.

Aber Ihr habt mir schon sehr viel geholfen, dafür danke!

Grüße,
Nesso