I am trying to use SQLCLR to get a list of files on another server. I have created an SQL Server Database Project to do this. Next, I have tried to whitelist my assembly using the method given here: https://nielsberglund.com/2017/07/23/sql-server-2017-sqlclr---whitelisting-assemblies/
EXEC sys.sp_add_trusted_assembly @hash = @hash,
@description = @clrName;
Now, when I do select * from sys.trusted_assemblies, my program appears in the list of trusted assemblies.
It does have permission-set-desc of UNSAFE_ACCESS.
Finally, I try to Publish it using VS2019.
Here, I get an error:
(47,1): SQL72014: .Net SqlClient Data Provider: Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly 'CLR_UDF' failed because assembly 'CLR_UDF' is not trusted. The assembly is trusted when either of the following is true: the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission, or the assembly is trusted using sp_add_trusted_assembly.
Just to clarify:
- My assembly IS signed with an SNK file.
- My assembly is listed as a trusted assembly in sys.trusted_assemblies
This issue has only arisen since adding code to authenticate with user name & password.
How do I publish my program?
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections;
using CLR_UDF;
public partial class UserDefinedFunctions
{
[SqlFunction(
Name = "os_directory_info",
FillRowMethodName = "os_directory_info_FillRow",
DataAccess = DataAccessKind.None,
IsDeterministic = true,
TableDefinition = @"name nvarchar(4000), is_directory bit, size_in_bytes bigint, create_date datetime,
last_written_to datetime, last_accessed datetime, attributes nvarchar(4000)")]
public static IEnumerable os_directory_info(SqlString path, SqlString filter)
{
UserImpersonation impersonator = new UserImpersonation();
_ = impersonator.impersonateUser("user name", "", "password"); //No Domain is required
DirectoryInfo di = new DirectoryInfo(path.Value);
FileSystemInfo[] w;
if (filter.IsNull || filter.Value == string.Empty)
w = di.GetFileSystemInfos();
else
w = di.GetFileSystemInfos(filter.Value);
return w;
}
private static void os_directory_info_FillRow(object obj,
out SqlString altname,
out SqlBoolean is_directory,
out SqlInt64 size,
out SqlDateTime createdatetime,
out SqlDateTime lastwritedatetime,
out SqlDateTime lastaccessdatetime,
out SqlString attrib)
{
if (obj is FileInfo)
{
FileInfo fsi = (FileInfo)obj;
altname = fsi.Name;
is_directory = SqlBoolean.False;
size = fsi.Length;
createdatetime = fsi.CreationTime;
lastwritedatetime = fsi.LastWriteTime;
lastaccessdatetime = fsi.LastAccessTime;
attrib = fsi.Attributes.ToString();
}
else
{
FileSystemInfo fsi = (FileSystemInfo)obj;
altname = fsi.Name;
is_directory = SqlBoolean.True;
size = SqlInt64.Null;
createdatetime = fsi.CreationTime;
lastwritedatetime = fsi.LastWriteTime;
lastaccessdatetime = fsi.LastAccessTime;
attrib = fsi.Attributes.ToString();
}
}
}
using System;
using System.Runtime.InteropServices;
using System.Security.Principal;
namespace CLR_UDF
{
class Impersonator
{
#region "Consts"
public const int LOGON32_LOGON_INTERACTIVE = 2;
public const int LOGON32_PROVIDER_DEFAULT = 0;
#endregion
#region "External API"
[DllImport("advapi32.dll", SetLastError = true)]
public static extern int LogonUser(string lpszUsername,
string lpszDomain,
string lpszPassword,
int dwLogonType,
int dwLogonProvider,
ref IntPtr phToken);
[DllImport("advapi32.dll", SetLastError = true)]
public static extern bool RevertToSelf();
[DllImport("kernel32.dll", SetLastError = true)]
public static extern int CloseHandle(IntPtr hObject);
#endregion
#region "Methods"
//Public Sub PerformImpersonatedTask(ByVal username As String, ByVal domain As String, ByVal password As String, ByVal logonType As Integer, ByVal logonProvider As Integer, ByVal methodToPerform As Action)
public void PerformImpersonatedTask(string username,
string domain,
string password,
int logonType,
int logonProvider,
Action methodToPerform)
{
IntPtr token = IntPtr.Zero;
if (RevertToSelf())
{
if (LogonUser(username, domain, password, logonType, logonProvider, ref token) != 0)
{
WindowsIdentity windowsIdentity = new WindowsIdentity(token);
dynamic identity = windowsIdentity;
WindowsImpersonationContext impersonationContext =
windowsIdentity.Impersonate();
//dynamic impersonationContext = identity.Impersonate();
if (impersonationContext != null)
{
methodToPerform.Invoke();
impersonationContext.Undo();
}
// do logging
}
else
{
}
}
if (token != IntPtr.Zero)
{
CloseHandle(token);
}
}
#endregion
}
public class UserImpersonation
{
const int LOGON32_LOGON_INTERACTIVE = 2;
const int LOGON32_LOGON_NETWORK = 3;
const int LOGON32_LOGON_BATCH = 4;
const int LOGON32_LOGON_SERVICE = 5;
const int LOGON32_LOGON_UNLOCK = 7;
const int LOGON32_LOGON_NETWORK_CLEARTEXT = 8;
const int LOGON32_LOGON_NEW_CREDENTIALS = 9;
const int LOGON32_PROVIDER_DEFAULT = 0;
const int LOGON32_PROVIDER_WINNT35 = 1;
const int LOGON32_PROVIDER_WINNT40 = 2;
const int LOGON32_PROVIDER_WINNT50 = 3;
WindowsImpersonationContext impersonationContext;
[DllImport("advapi32.dll", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true)]
public static extern int LogonUserA(string lpszUsername, string lpszDomain, string lpszPassword, int dwLogonType, int dwLogonProvider, ref IntPtr phToken);
[DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true, ExactSpelling = true)]
public static extern int DuplicateToken(IntPtr ExistingTokenHandle, int ImpersonationLevel, ref IntPtr DuplicateTokenHandle);
[DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true, ExactSpelling = true)]
public static extern long RevertToSelf();
[DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true, ExactSpelling = true)]
public static extern long CloseHandle(IntPtr handle);
public bool impersonateUser(string userName, string domain, string password)
{
return impersonateValidUser(userName, domain, password);
}
public void undoimpersonateUser()
{
undoImpersonation();
}
private bool impersonateValidUser(string userName, string domain, string password)
{
bool functionReturnValue = false;
WindowsIdentity tempWindowsIdentity = null;
IntPtr token = IntPtr.Zero;
IntPtr tokenDuplicate = IntPtr.Zero;
functionReturnValue = false;
//if (RevertToSelf()) {
if (LogonUserA(userName, domain, password, LOGON32_LOGON_NEW_CREDENTIALS, LOGON32_PROVIDER_WINNT50, ref token) != 0)
{
if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)
{
tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);
impersonationContext = tempWindowsIdentity.Impersonate();
if ((impersonationContext != null))
{
functionReturnValue = true;
}
}
}
//}
if (!tokenDuplicate.Equals(IntPtr.Zero))
{
CloseHandle(tokenDuplicate);
}
if (!token.Equals(IntPtr.Zero))
{
CloseHandle(token);
}
return functionReturnValue;
}
private void undoImpersonation()
{
impersonationContext.Undo();
}
}
}
If I run without the extra bits to log my in to the other server, I get this error:
Msg 6522, Level 16, State 1, Line 3
A .NET Framework error occurred during execution of user-defined routine or aggregate "os_directory_info":
System.IO.IOException: The user name or password is incorrect.
System.IO.IOException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileSystemEnumerableIterator`1.CommonInit()
at System.IO.FileSystemEnumerableIterator`1..ctor(String path, String
originalUserPath, String searchPattern, SearchOption searchOption, SearchResultHandler`1 resultHandler, Boolean checkHost)
at System.IO.DirectoryInfo.InternalGetFileSystemInfos(String searchPattern, SearchOption searchOption)
at UserDefinedFunctions.os_directory_info(SqlString path, SqlString filter)
Usage:
In SSMS:
Select * from dbo.os_directory_info('c:\', default)
