TryCatchFinally.net Some SQL, some .NET, and whatever else

2Mar/130

Refreshing changed .NET SQL CLR assemblies after patching/updates

After applying some Windows updates to one of my servers, I started getting the following error when I ran a customer .NET SQL-CLR stored proc:

Server: Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'somemethodyourecalling':

System.IO.FileLoadException: Could not load file or assembly 'System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)

I'd imported some additional assemblies into SQL Server for use in SQL CLR mapped stored procedures, and the Windows updates had included a service pack for .NET, which changed some of the assemblies. Now the version I'd linked to SQL Server no longer existed on disk, and SQL couldn't load it - that made sense, and is documented pretty clearly in this MS Support article: http://support.microsoft.com/kb/949080

However, I had dozens of servers with SQL CLR components enabled, and hundreds of different assemblies loaded across them all, and not always the same in each server, so a standard update script wouldn't work to refresh all the changed assemblies (the MS Support link provides a list of the standard ones that cause that error, but if you've got custom assemblies loaded, or you've loaded an assembly that's not specifically cleared for SQL CLR, then it's not on the list either). To deal with this, I wrote a script that fetches the assembly list for a database and attempts to refresh every one of them from their disk location. If they haven't changed, the update attempt will fail with a specific error message about the MVID, and there's no change for that assembly.

Also, I've commented out the line that restricts it to just framework assemblies (System.* and Microsoft.*), but you can uncomment that line if you'd like to restrict the refresh from attempting to reload your custom assemblies as well.

DECLARE @AssemblyName VARCHAR(255),
	    @AssemblyLocation VARCHAR(255),
	    @AlterAssemblyCommand NVARCHAR(1024),
	    @DotNetFolder VARCHAR(100)

   SET @DotNetFolder = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727'

CREATE TABLE #Results (
		AssemblyName VARCHAR(255),
		AssemblyLocation VARCHAR(255),
		AlterAssemblyCommand NVARCHAR(1024),
		Results VARCHAR(1024)
)

select sa.name as AssemblyName,
		saf.name as Assemblylocation,
		case when charindex('', saf.name) = 0
			then 'ALTER ASSEMBLY [' + sa.name + '] FROM ''' + @DotNetFolder
			else 'ALTER ASSEMBLY [' + sa.name + '] FROM '''
		end + saf.name + (case right(saf.name, 4) when '.dll' then '' else '.dll' end) + ''''
		as AlterAssemblyCommand
INTO #Refresh
from sys.assemblies sa
join sys.assembly_files saf
  on sa.assembly_id = saf.assembly_id
where sa.name <> ('Microsoft.SqlServer.Types')
  --and (sa.name like 'System.%' or sa.name like 'microsoft.%')

DECLARE Commands CURSOR FAST_FORWARD FOR
SELECT AssemblyName,
	   AssemblyLocation,
	   AlterAssemblyCommand
  FROM #Refresh

OPEN Commands

FETCH NEXT FROM Commands
INTO @AssemblyName,
	   @AssemblyLocation,
	   @AlterAssemblyCommand

WHILE @@FETCH_STATUS = 0
BEGIN

	BEGIN TRY
		exec sp_executesql @AlterAssemblyCommand

		insert into #Results
		SELECT @AssemblyName,
				@AssemblyLocation,
				@AlterAssemblyCommand,
				'Assembly refreshed successfully'

	END TRY
	BEGIN CATCH

		insert into #Results
		SELECT @AssemblyName,
				@AssemblyLocation,
				@AlterAssemblyCommand,
				CASE ERROR_NUMBER()
					WHEN 6285 THEN 'No update necessary (MVID match)'
					WHEN 6501 THEN 'Physical assembly not found at specified location (SQL Error 6501)'
					ELSE ERROR_MESSAGE() + ' (SQL Error ' + convert(varchar(10), ERROR_NUMBER()) + ')'
				END

	END CATCH

	FETCH NEXT FROM Commands
	INTO @AssemblyName,
		   @AssemblyLocation,
		   @AlterAssemblyCommand

END

CLOSE Commands
DEALLOCATE Commands

SELECT * FROM #Results

drop table #refresh
drop table #Results

While troubleshooting the error, I came across this as well - I don't have a SQL 2012 server handy to check with, it looks like this problem might be resolved with a reboot in SQL 2012:

http://msdn.microsoft.com/en-us/library/hh479773.aspx

I'm not sure if that's the case, but it would make things easier. Also, it would be nice if there was some kind of "ALTER ASSEMBLY [SomeAssembly] REFRESH" command to address this problem, but maybe in a future version.

Additionally, this error can be caused if the signing of an assembly has changed, but not necessarily the signature, but just reloading it from disk won't work because the method that SQL Server uses to determine if there's been a change is the MVID, and that doesn't change unless there's been a code change of some kind (see the Connect bug here). In those cases, you'll actually need to drop and recreate the assembly, including any supporting objects that rely on that assembly.

9Dec/110

Dealing with an exception: “An attempt was made to access a socket in a way forbidden by its access permissions”

I was attempting to bind a remoting listening to a particular port and kept receiving an exception when attempting to bind on the production Windows 2008 R2 server itself - it always worked fine on both my development box and our test server. Here's the exception:

Exception message: An attempt was made to access a socket in a way forbidden by its access permissions
Stack Trace:
   at System.Net.Sockets.Socket.DoBind(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.Sockets.Socket.Bind(EndPoint localEP)
   at System.Net.Sockets.TcpListener.Start(Int32 backlog)
   at System.Runtime.Remoting.Channels.ExclusiveTcpListener.Start(Boolean exclusiveAddressUse)
   at System.Runtime.Remoting.Channels.Tcp.TcpServerChannel.StartListening(Object data)
   at System.Runtime.Remoting.Channels.Tcp.TcpServerChannel..ctor(IDictionary properties, IServerChannelSinkProvider sinkProvider, IAuthorizeRemotingConnection authorizeCallback)

Though Googling gave some suggestions to run the process as an administrator (no effect), the actual problem was that my process was trying to listen on a port that was already taken by another listener. Since the port was already in use, it couldn't bind and I received the exception. Stopping the other process resolved the issue immediately.

Not really a clear error message, since it really had nothing to do with permissions at all, but there you go.

16Aug/100

Reading from C:\ProgramData without requiring UAC elevation

When trying to read some user settings from C:\ProgramData in my .NET app, I was getting an Access Denied exception, even though I was only attempting to read the configuration file, not write anything:

System.UnauthorizedAccessException: Access to the path 'C:\ProgramData\YourApp\1.0.0.0\settings.xml' is denied.

Even though I was only reading the file, and not writing anything, it still wanted elevation before it let me read it. It turns out that I need to signify my intent not to write anything when I open the stream. This code generated the exception (the "Using" statement actually threw the exception):

Using f As New FileStream(MySettingsFilePath, FileMode.Open)
    Dim formatter As New Formatters.Binary.BinaryFormatter
    MySettings = formatter.Deserialize(f)
    f.Close()
End Using

However, by changing the "FileStream" to a "StreamReader", I signify my intent to read and not write, so the code runs without an issue (there are two changes):

Using f As New StreamReader(MySettingsFilePath)
    Dim formatter As New Formatters.Binary.BinaryFormatter
    MySettings = formatter.Deserialize(f.BaseStream)
    f.Close()
End Using
MORAL - Elevation isn't required to read common application settings, only to write them, but you need to be clear about what you intend to do!
Tagged as: No Comments
15Jun/100

Periodic timeouts with local WCF endpoint

I have two applications - a Windows service and a client WinForm - that run on the same box, and the client needs to check on the status of the service every few seconds. It worked well most of the time, but every 4-5 times, it would timeout - I'd receive a System.TimeoutException, no matter how long the timeout was actually set for. It didn't make sense - the two apps are on the same box, it happens with both a TCP and an NamedPipe endpoint, and it can happen even when the service isn't busy.

It turns out that WCF endpoints only handle a very limited number of simultanious connections, and though I was creating a new ChannelFactory each time, I wasn't closing the channel when I was finished with it. I assumed that it would automatically close the channel when it passed out of scope, but no dice. Since the channels remained open, it didn't take long to fill them all up, and then the app would appear to be unresponsive until my channels started to time out. So here's what I ended up doing - critical line in bold, at the end:

Dim tcpBinding As New NetTcpBinding
Dim pipeFactory As ChannelFactory(Of WCF_Class.IServiceRemoting) = New  _
    ChannelFactory(Of WCF_Class.IServiceRemoting)(tcpBinding, "net.tcp://localhost:4079")
Dim ServiceWCFConnection As WCF_Class.IServiceRemoting = pipeFactory.CreateChannel
MessageBox.Show(ServiceWCFConnection.Responsive)
pipeFactory.Close() ' This is what's important!

That's it - make sure you close your Channel when you're done with it! If you leave it open, you quickly hit the server's connection limit and new requests will fail. Closing the channel when you're done frees it up, instead of letting it time out and close on its own.

Tagged as: , No Comments
25Mar/101

Visual Studio solutions don’t load when you double-click on them

When I installed Windows 7, double-clicking on .SLN files to load them in Visual Studio stopped working - I would get the hourglass for a few moments, and then nothing. It turns out that it's because I had set Visual Studio to always run as an Administrator, but when you double-click a .SLN file, you're not actually running Visual Studio - you're running the "Visual Studio Version Selector", even if you only have one version of Visual Studio installed.

To resolve the problem, you'll need to set the Version Selector to also run as an Administrator - to do this, find the Version Selector EXE:

C:\Program Files\Common Files\microsoft shared\MSEnv\VSLauncher.EXE (or "Program Files (x86)" if you have an x64 installation)

Right-click on it, go to the "Compatibility" tab, and check the box that says "Run this program as an administrator". Since I have multiple users on my laptop that I use for development, I had to click "Change Settings for all users" before checking the "Run as admin" box.

Voila! The VS Version Selector will now properly load the solution files when you double-click on them - enjoy!

2Jul/090

Encrypting data per-user in your .NET application without asking for a secret

When you encrypt something, you need some kind of secret to do so - a password that's used to encrypt and decrypt your message, or a public/private key like a certificate. In order to get a secret, you usually have to ask the user for one - in most cases, a password - before they can decrypt their data and access it.

I had an application where I wanted to encrypt some database connection details between sessions. This is easy enough, but I wanted to do it without having to ask the user for a password - I just wanted it done transparently. I thought this meant storing a secret key somewhere, but there's no safe place to keep a key -even if it's embedded in your code, somebody who really wants it will always be able to get it. What I needed was a private place to store a key, where no other Windows user on the same computer would be able to access it.

That's when I discovered the DPAPI - it's been around since .NET 2.0, and it's available through the System.Security.Cryptography namespace. What's great about this cryptographic feature is that you can have Windows encrypt something with the same key it uses to encrypt files with EFS - a private user key that's based on a hash of their Windows password. This means that you don't need to ask the user for a secret - you've already got one handy, and Windows will prevent other users of the system from being able to decrypt your data. The downside of this is that if the user resets their password, you're toast - if you're only keep the data as a convenience (as I am), that's no problem, but if you can't afford to lose the data, you'll need to ask the user for the secret instead of relying on this method.

This MSDN article that detailed its use, and after adding some code that made it easy to encrypt and decrypt strings, here's what I ended up with:

Imports System
Imports System.Security.Cryptography

''' <summary>
''' Encrypts and Decrypts information using the current Windows user key
''' </summary>
''' <remarks></remarks>
Public Class DPAPI

    Private Shared EntropyString As String = "Some value I made up"

    ' Create byte array for additional entropy when using Protect/Unprotect method.
    Private Shared Function AdditionalEntropy() As Byte()
        Dim encoder As New System.Text.ASCIIEncoding
        Return encoder.GetBytes(EntropyString)
    End Function

#Region " Encrypt "

    Public Shared Function Protect(ByVal data() As Byte) As Byte()
        Try
            ' Encrypt the data using DataProtectionScope.CurrentUser. The result can be decrypted
            ' only by the same current user.
            Return ProtectedData.Protect(data, AdditionalEntropy, DataProtectionScope.CurrentUser)
        Catch e As CryptographicException
            Console.WriteLine("Data was not encrypted. An error occurred.")
            Console.WriteLine(e.Message.ToString())
            Throw
        End Try
    End Function

    Public Shared Function ProtectString(ByVal data As String) As String

        Dim encoder As New System.Text.ASCIIEncoding
        Return Convert.ToBase64String(Protect(encoder.GetBytes(data)))

    End Function

#End Region

#Region " Decrypt "

    Public Shared Function Unprotect(ByVal data() As Byte) As Byte()
        Try
            'Decrypt the data using DataProtectionScope.CurrentUser.
            Return ProtectedData.Unprotect(data, AdditionalEntropy, DataProtectionScope.CurrentUser)
        Catch e As CryptographicException
            Console.WriteLine("Data was not decrypted. An error occurred.")
            Console.WriteLine(e.Message.ToString())
            Throw
        End Try

    End Function

    Public Shared Function UnprotectString(ByVal data As String) As String

        Dim encoder As New System.Text.ASCIIEncoding
        Dim b() As Byte = Convert.FromBase64String(data)
        Return encoder.GetString(Unprotect(b))

    End Function

#End Region

End Class

Download the code here

To use the class, just change the entropy value at the top (this is combined with the user's key to create a new key used to encrypt your data), call Protect() or ProtectString() and pass it the required data, and you're good to go! I added the ProtectString and UnprotectString because I was storing the values in the user's app.config file, and needed an easy string representation.

If you use the class or have any questions, please let me know!

18Mar/092

Accessing System.DirectoryServices from SQL Server 2005

SQL Server 2005 allows for the integration of .NET assemblies into the databases so that they can be accessed from inside stored procedures and other database functions. Although this is a great new feature, I got hung up on a particularly cryptic error message when I tried to build an assembly and import it.

Since SQL Server makes it difficult to query active directory, and I wanted to build an AD-based authentication module for my database application, the best way to do that seemed to be to use this new feature. My assembly depended on System.DirectoryServices in order to access Active Directory, but that wouldn't be a problem, since the .NET 2.0 framework is available from inside SQL Server 2005 (http://msdn2.microsoft.com/en-us/library/ms254506.aspx, provided you've enabled the feature), right? Well, sort of. As it turns out, SQL Server was rushed to RTM too quickly for all of the .NET 2.0 assemblies to be cleared as SAFE, so the ones that weren't fully tested aren't included by default. Fair enough - so it's just a matter of importing System.DirectoryServices, and then importing my assembly that relies on it, right? Again, sort of.

System.DirectoryServices can be imported into SQL Server, but only as an UNSAFE assembly. This has all sorts of other security implications (which is a little ironic, since I was using it to verify user security), but I decided to use it anyway, since I figured that the UNSAFE tag was more of a formality than a real danger, and the assembly would be SAFE once more testing had been done. I imported System.DirectoryServices:

USE master
GO

CREATE ASYMMETRIC KEY asmKey_DirectoryServices
FROM EXECUTABLE FILE = 'c:\Windows\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll'
GO

CREATE LOGIN asmLogin_DirectoryServices
FROM ASYMMETRIC KEY asmKey_DirectoryServices
GO

GRANT unsafe ASSEMBLY TO asmLogin_DirectoryServices
GO

That imports the System.DirectoryServices assembly as UNSAFE. Next, I imported my assembly as SAFE, since it was signed. The only problem was that when I called my assembly, which reached into System.DirectoryServices, I got an error (I'm calling clrIsMemberOfGroup in my assembly, SqlHelper):

After 4 hours in the phone troubleshooting the issue with Microsoft, it turned out that it was VERY simple, and even vaguely alluded to in a knowledgebase document. In order to reach into the UNSAFE System.DirectoryServices assembly, I had to make my assembly UNSAFE as well. Since the UNSAFE assembly was running outside the bounds of what .NET considers "SAFE", it could potentially return suspect results, and so anything that relies directly on those results couldn't be considered "SAFE", and had to be tagged as "UNSAFE". It seems like I should be able to implement proper sanitizing code in my assembly so that I don't inherently trust the results from my "UNSAFE" assembly, but SQL Server would have none of it. In order to reach into an UNSAFE assembly, I needed to flag my assembly as UNSAFE - simply placing my assembly import into a "Create key, create login, import assembly" setup like the one I used for System.DirectoryServices fixed the problem.

I suppose the question is really "Did that fix anything?" since all I really did was disable security on those assemblies. It's really hard to throw a security assembly when you don't do any sort of security checks. Well, at least I alleviated the symptoms, and now I'll just wait for SP1 to (hopefully) add System.DirectoryServices (among other missing framework assemblies) to the assemblies accessible from inside the CLR access in SQL Server 2005. I suppose we'll have to wait and see...