Problem with CLR Assembly call
-
I have a very simply SQLCLR project.
using System;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
namespace CmdShell
{
public class CmdShellBridge
{
[SqlProcedure]
public static int processCMD(string fileName, string arguments)
{
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.StartInfo.FileName = fileName;
proc.StartInfo.Arguments = arguments;
try
{
proc.Start();
proc.WaitForExit();
}
catch (Exception e)
{
return -1;
}
return 0;
}
};
}
It will be used to replace xp_cmdshell calls in our stored procedures.
I followed all steps from http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/10/29/deploying-sql-clr-assembly-using-asymmetric-key.aspx, but now I have a problem when I try to execute stored procedures in which I use my new extended procedure (processCMD).
I used this script to create assembly.
USE master
GO
IF NOT EXISTS(select * from sys.asymmetric_keys where name = 'SQLCLRTestKey')
CREATE ASYMMETRIC KEY SQLCLRTestKeyFROM EXECUTABLE FILE = 'C:\Test\CmdShellBridge.dll'
go
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin
GO
USE cssprod
GO
CREATE USER SQLCLRTestLogin FOR LOGIN SQLCLRTestLogin
GO
use cssprod
CREATE ASSEMBLY CmdShellBridge
FROM 'C:\Test\CmdShellBridge.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
And after this somebody grant EXTERNAL ACCESS ASSEMBLY to my account too.
GRANT EXTERNAL ACCESS ASSEMBLY TO myaccount
I execute the stored procedure with my account and the error message was:
Msg 6522, Level 16, State 1, Procedure processCMD, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "processCMD":
System.Security.SecurityException: Request failed.
System.Security.SecurityException:
at CmdShell.CmdShellBridge.processCMD(String fileName, String arguments)
.
What can be the problem in my case ?
-
just like xp_cmdshell, the service account that SQL is using is the credentials used for accessing objects outside of SQL server.
if your process was accessing local folders , special folders like [my documents], the root of C:, network folders, other objects, the registry, etc it might not have permissions.
take a look at Administrative Tools>>Services, and see what account the service is running under; would that account be able to do the work your call is doing(ie has access to some network share or something?
,
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
SQL Servers run under Local System account.
-
levaif (8/10/2015)
SQL Servers run under Local System account.right, so local system does not have domain access to any network shares, for example. it cannot browse to \\machinename\d$\somefolder.
to do that, you must change the account.
what, specifically, is this test call to your process doing? a call to powershell or robocopy, for example?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
We have several stored procedures. In these sp's we want to made some IO operations with bcp, osql or other utilities.
Example :
SET @ExecCommand ="SELECT * from ##temp_CH" queryout \\WYVSTSITTS01\backoffice\ExportImport\FIRE\CEGBIROSAG_20150808.txt -c -S WYVSTSITCSS01\CSS_SQL01 -t -r -t; -T -C1250
EXEC @result = dbo.processCMD @@PROCID, 'bcp.exe ', @ExecCommand
-
levaif (8/10/2015)
We have several stored procedures. In these sp's we want to made some IO operations with bcp, osql or other utilities.Example :
SET @ExecCommand ="SELECT * from ##temp_CH" queryout \\WYVSTSITTS01\backoffice\ExportImport\FIRE\CEGBIROSAG_20150808.txt -c -S WYVSTSITCSS01\CSS_SQL01 -t -r -t; -T -C1250
EXEC @result = dbo.processCMD @@PROCID, 'bcp.exe ', @ExecCommand
if you were to change the code to write to a local drive (ie c:\Data\CEGBIROSAG_20150808.txt) your bcp would work.
because SQL is running under the local system account, and you are trying to access a server share, you must change the startup account to a domain account. Local system would have access to most local drives, but nothing on the network.
\\WYVSTSITTS01\backoffice\ExportImport\FIRE\CEGBIROSAG_20150808.txt
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
It's something which is not clear for me.
So, SQL server is running under Local system account, on an terminal server.
I use my domain account to log into the server, and also I can connect to SQL server via SQL Server Manager.
Then I try to execute the stored procedure.
My account has EXTERNAL ACCESS grant to ASSEMBLY.
The problem is, that SQL Server runs under local system account, not under my domain account ?
-
levaif (8/10/2015)
It's something which is not clear for me.So, SQL server is running under Local system account, on an terminal server.
I use my domain account to log into the server, and also I can connect to SQL server via SQL Server Manager.
Then I try to execute the stored procedure.
My account has EXTERNAL ACCESS grant to ASSEMBLY.
The problem is, that SQL Server runs under local system account, not under my domain account ?
that is correct.
SQL does not care if you are local administrator, domain administrator or anything when it has to touch objects OUTSIDE of SQL server;
inside sql, in databases and such, it certainly uses your credentials to determine access.
OUTSIDE of SQL server, it is one specific account that is used, and that is the account under which the service runs.
SQL doesn't have a mechanism to pass your credentials to xp_cmdshell or external processes like CLR, so it uses it's own.
this is one of those annoying permissions gotchas you encounter related to SQL.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I'm still not 100 percent clear in this problem.
I verified that SQL Server runs under Local system account.
I publish my assembly with EXTERNAL ACCESS permission set and I tried to run an stored procedure where I use a call using one stored procedure from my assembly. As I expected I still got an error:
Msg 6522, Level 16, State 1, Procedure processReturn, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "processReturn":
System.Security.SecurityException: Request failed.
System.Security.SecurityException:
at CmdShell.CmdShellBridge.processReturn(String fileName, String& ReturnMessage)
When publish the assembly with UNSAFE permission set, the stored procedure runs without any problem, I can access external files, I can read from, and write to it.
Using whoami.exe I found that the user in which name runs the stored procedure was :
nt authority\system
Because I have no experience in security problems, it would be useful for me some an example how can I fix my problem.
We have over than 30 stored procedures in which I have to use my assembly. This procedures has to access many external files for read and write purpose. For these external folders I have to add permissions for a large number of users. I understand that I could use credentials for not to set those permissions for users individually, but I don't know exactly how to do it.
I have to resolve my problem with minimal security issues.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply