Tag: .NET

SQL Agent job fails with QUOTED_INDENTIFIER error

If you have a SQL Agent job that’s failing, but the query succeeds when you run it manually in SQL Management Studio, it’s possibly a difference between the SQL Server default connection settings and those SSMS uses as the default. In my case, I was inserting from a stored procedure and received the following error:

[SQLSTATE 42000] (Error 1934) INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

To view the default connection options for your SQL Server instance, right-click on your instance in SSMS and select “Properties”, and then browse to “Connections” – you’ll see something like the following list (on my server, none of these are enabled by default for new connections):

Server Defaults

To compare it to the default settings for your installation of SSMS, click on “Tools” and then “Options…”, and then browse to “Query Execution” -> “SQL Server” and view the settings under both “Advanced” and ANSI” (In my case, SSMS was enabling a number of settings by default for my connections that SQL Agent jobs wouldn’t have enabled):

SSMS Results ANSI

SSMS Results Advanced

In my case, I just added an explicit “SET QUOTED_IDENTIFIER ON” to the beginning of the script in my SQL Agent job and it resolved the error message. Hope this helps you too!

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.

“Invalid server” error while altering SQL Agent Job step

When updating a SQL Agent job step to run an SSIS package instead of executing an SSAS script, I received the following error when trying to save the Agent Job:

The specified ‘@server’ is invalid (valid values are returned by sp_helpserver).
The specified '@server' is invalid (valid values are returned by sp_helpserver)

I initially thought this error had something to do with my package connections, but it didn’t – it occurred because I had changed the job step from an SSAS script to an SSIS package, rather than creating a new step and removing the old one. This actually seemed to occur when I alter the “SQL Server Analysis Services Command” step to any other type of step – it must leave some portion of the step partially configured, and it throws a validation error when you try to save it.

Solution:

To work around this error, delete the old “SSAS Command” step, rather than modifying it, and created a new step to run your SSIS package. It will validate without error and let you save the updated package.

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!

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!