1

I have created .NET SQLCLR stored procedure, and built and published it on my local SQL Server.

In the .NET code I am using this connection string:

OleDbConnection connection = new OleDbConnection(
      "provider=MSOLAP.7;data source=(local);initial catalog=AdventureWorksDW2014");

This .NET code contains a cube update query. Thus, the assembly needs to update the cube hosted on my local Analysis server.

When executing the SQLCLR stored procedure in SSMS I get the following error:

Msg 6522, Level 16, State 1, Procedure dbo.SqlStoredProcedure1, Line 0

[Batch Start Line 0] A .NET Framework error occurred during execution of user-defined routine or aggregate "SqlStoredProcedure1":
System.Security.SecurityException: Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.PermissionSet.Demand() at System.Data.Common.DbConnectionOptions.DemandPermission() at System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection outerConnection) at
System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at StoredProcedures.SqlStoredProcedure1()

For some reason, the OleDb connection isn't working from the assembly. But, when debugged from Visual studio, the code runs perfectly fine and updates the cube.

Please share with me the potential fixes to the above error.

Improve this question
2
  • As an aside, you don't actually need a CLR stored procedure for this, unless the .NET code does things T-SQL can't do (and if that's the case, a client application is probably more appropriate than a CLR procedure). You can create a linked server and then use EXECUTE .. AT to issue any XMLA batch (and OPENROWSET for MDX). Commented Jul 4, 2018 at 9:50
  • Agreed. But for writeback to happen, it needs to be done in one session. Begin Transaction->Update->Commit Transaction. And Linked server only uses scalar queries for each session. I cannot fire 3 queries one single session using linked server. That is the sole reason why I opted CLR stored procedure. Commented Jul 6, 2018 at 13:08

1 Answer 1

Reset to default
1

You are getting a security permission error when trying to open the connection. The connection is external (i.e. not using the internal "context connection"), so the Assembly needs to have a PERMISSION_SET of at least EXTERNAL_ACCESS. But, in order to do that, you need to sign the Assembly (since you are using Visual Studio, this means giving it a strong name), create an Asymmetric Key in master from that strong name key, create a Login from that Asymmetric Key, and grant that Login the EXTERNAL ACCESS ASSEMBLY permission. Or, if you are using SQL Server 2017 (or newer), then that last step changes to be granting that Login the UNSAFE ASSEMBLY permission.

After doing those steps, then you can either create the Assembly WITH PERMISSION_SET = EXTERNAL_ACCESS, or ALTER ASSEMBLY [{assembly_name}] WITH PERMISSION_SET = EXTERNAL_ACCESS;

For detailed instructions, including how to automate this in Visual Studio, with the ability to have it work in SQL Server 2017 and beyond, and without relying upon external files beyond the T-SQL script, please see the following two posts of mine:

Solution 1 describes how to accomplish this assuming you want to stay fully within the strong name key / Asymmetric Key framework used by Visual Studio. Solution 2 describes a less complicated approach using only Certificates.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

In your Solution 1 ; When I typed "MAKECERT -r -pe -n "CN=SqlQuantumLeap.com" -e "12/31/2099" -sv SQL2017-ClrStrictSecurity-Cert.pvk SQL2017-ClrStrictSecurity-Cert.cer (password = blah)" in cmd promt I got the following error: C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL>MAKECERT -r -pe -n "CN=SqlQuantumLeap.com" -e "12/31/2099" -sv SQL2017-ClrStrictSecurity-Cert.pvk SQL2017-ClrStrictSecurity-Cert.cer Error: Unable to create file for the subject ('SQL2017-ClrStrictSecurity-Cert.pvk') Error: Can't create the key of the subject ('SQL2017-ClrStrictSecurity-Cert.pvk') Failed
@Mariam HI there. If you copied the (password = blah) part, then get rid of that. That is just an indication to readers of the password that I used since it shows up in command line examples that follow. But it is not part of that command line.
I tried without the password=blah .. It still throws up the error
@Mariam I am not sure what we are doing differently, but I just copied and pasted the command from your first comment above, minus the "(password = blah)" part, and it worked as expected. I used, on a single command-line: MAKECERT -r -pe -n "CN=SqlQuantumLeap.com" -e "12/31/2099" -sv SQL2017-ClrStrictSecurity-Cert.pvk SQL2017-ClrStrictSecurity-Cert.cer. The error message you gave does not make sense. It says that the subject is "('SQL2017-ClrStrictSecurity-Cert.pvk')" which is strange since that is a file name, and the subject is "CN=SqlQuantumLeap.com", which you should change to your name.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.