Cannot Deploy Reports Using Custom Assembly - "Access is Denied"

We have a custom assembly that worked fine with SSRS 2000. Now that we have upgraded to SQL 2005 we get an "Access is denied" error when attempting to deploy reports that reference this assembly. I could swear that when we first attempted it it worked. We decided to make a minor change to the assembly code and that's when the problem started, even though I've since reverted back to the original code to make certain that the minor change wasn't the issue. The assembly is in the proper folder - C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\bin - and the reports are referencing the current version. I've searched high and low on the web for an answer but have found nothing even remotely helpful. All this code does is return a string...it's not doing anything that should require any additional security; and even then I doubt if there would be a problem when deploying.

The full text of the error is:

Error while loading code module: ‘Reporting, Version=1.0.2778.12272, Culture=neutral, PublicKeyToken=null’. Details: Could not load file or assembly 'Reporting, Version=1.0.2778.12272, Culture=neutral, PublicKeyToken=null' or one of its dependencies. Access is denied.

Any ideas? Anyone? I'm at my wit's end on this!

Thanks,

Mike

Please read this.

http://www.codeproject.com/useritems/Reporting_Services.asp

Priyank

|||Have you deployed your assemby dll to the GAC or it is in the File System.
If you have it in the files system be sure it is also in the folder. Install path / ReportingServices/ReportServer/bin and / ReportingServices/ReportManager/bin|||

Thanks, but I've tried this and it made no difference.

The code was originally developed in VS2003 and deployed to a SQL 2000 server, just as in the linked article. All we did was copy the dll to the appropriate folder on the server and it worked. The difference now is that we're using VS2005 and SQL 2005. The only other difference is that formerly SRSS and our SQL db's all resided on the same server and now they are on separate servers. I've copied the dll only to the SRSS server. Should it also be on the db server?

|||

gatharia wrote:

Have you deployed your assemby dll to the GAC or it is in the File System.
If you have it in the files system be sure it is also in the folder. Install path / ReportingServices/ReportServer/bin and / ReportingServices/ReportManager/bin

It's in the file system and resides in the appropriate folder. It's exactly as it was when we were using VS2003 and SQL 2000. I can't help but think it's server related, because the only difference (other than the aforementioned versions) is that we've migrated to new servers and whereas we formerly had only the one server with SRSS and our db's, they now reside on different servers.

|||I do not think you should copy to the db server. When moving your code to VS 2005 did you create a different strong name? If so then your public token may be different and this may cause issues. If the public token is different or same be sure that you have added a strong name blob to the following files. Installpath/ReportServer/rssvpolicy.config and Installpath/reportmanager/rsmgrpolicy.config. To do so bakup your config files, open the files in your editor and locate the last entry of </CodeGroup>
</CodeGroup>

Above this add a code group above the above lines like
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="CustomAssembly"
Description="This code group grants My cusstom Assembly permissions. ">
<IMembershipCondition
class="StrongNameMembershipCondition"
version="1"
PublicKeyBlob="0x0024000004800000940000000602000000240000525341310004000001000100AFEAEE95A2C6AE85CCAD6B8FA54E79514BB1690FD4803F93B0033B101A90107873E90511CF5A89BF624961DDAC82B1D10E89F991403B98F18C2CFCF87B8C09CCFA4AF20C9B3DF1DD6DBCA4A3AE2854030DDCE3313B1B9610F922B9881C9ABE05EC2AE451F1C20D42FE753F05E278EC964C5B9580E04090507C2A8B336BE3C7B5"
/>
</CodeGroup>

You MUST replace the PublicKeyBlob with the blob for your assembly. To get this blob use the command
SECUTIL.EXE -s assemblyname.dll > blob.txt

This send the blob to the file blob.txt
Add your assembly to the GAC instead of file system. Stop the RS process and restart.

Cannot deploy assembly from VS.NET into SQL server 2005

Hi,
when deploying assembly from VS into SQL Server 2005, deployment fails with these error messages:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

I'm deploying assembly with UNSAFE permission setting.
What's wrong, please?

Thanx, Fipil

Can you:
1. Post or describe your code?
2. See if the same error happens when you run CREATE ASSEMBLY, CREATE FUNCTION etc. in Management Studio, rather than deploying through Visual Studio?
3. Is this on SQL Server/VS RTM, or CTP builds? OS and processor architecture?
Thanks|||

1. Here is my code

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Cryptography;
using System.Security.Cryptography.Pkcs;
using System.Security.Cryptography.X509Certificates;
using System.Text;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetRandomPassword()
{
Random rnd=new Random();
string guid=Guid.NewGuid().ToString();
string pass = "";
for (int i = 0; i < 20; i++)
{
pass += guid[rnd.Next(guid.Length)];
}
return new SqlString(pass);
}

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBinary EncryptToken(string loginName, string password, byte[] bCert)
{
X509Certificate2 cert = new X509Certificate2(bCert);
string token = string.Format("{0};{1};{2}", loginName, password, cert.Thumbprint);
return new SqlBinary(encryptByCertPBK(cert,token));
}

private static byte[] encryptByCertPBK(X509Certificate2 cert, string msg)
{
Console.Write("Encrypting '{0}'... ", msg);
RSACryptoServiceProvider rsa = (RSACryptoServiceProvider)cert.PublicKey.Key;

byte[] textBytes = Encoding.UTF8.GetBytes(msg);
byte[] reth = rsa.Encrypt(textBytes, false);
Console.WriteLine("Done.");
return reth;

}

};

2. YES, the same error happens when I run CREATE ASSEMBLY in Management Studio:

CREATE ASSEMBLY DbTisSecurity
FROM 'D:\Test\DbFunctions.dll'
WITH PERMISSION_SET=UNSAFE;

3. I'm using the SQL Server 2005, Developer Edition, 32 bit, ver. 9.00.1399.06
OS: Win XP SP2, Czech version,
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional

|||

This seems to work fine for me. However, I don't happen to have the czech version of Windows XP handy to test this on.

Can you create and deploy other assemblies without seeing this error?

|||

When I create a new database, I can deploy this assembly without problem. But, when I'm working with this db, I'm creating and modifying db objects (tables, users, etc.), because of developing an application, after some time (a few days) the database becomes a descripted state. Suddenly I cannot deploy the assembly. I can deploy it with SAFE permission set, but I cannot deploy it with UNSAFE.

On SQL server logs I found this message:

SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Here is SQL DUMP:

2006-01-10 09:57:36.85 Server Error: 17310, Severity: 20, State: 1.
2006-01-10 09:57:36.85 Server A user request from the session with SPID 54 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
2006-01-10 09:58:41.79 spid57 Using 'dbghelp.dll' version '4.0.5'
2006-01-10 09:58:41.81 spid57 ***Stack Dump being sent to C:\...\LOG\SQLDump0025.txt
2006-01-10 09:58:41.81 spid57 SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2006-01-10 09:58:41.81 spid57 * *******************************************************************************
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * BEGIN STACK DUMP:
2006-01-10 09:58:41.81 spid57 * 01/10/06 09:58:41 spid 57
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * Exception Address = 01C364AC Module(sqlservr+00C364AC)
2006-01-10 09:58:41.81 spid57 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2006-01-10 09:58:41.81 spid57 * Access Violation occurred reading address 000000B4
2006-01-10 09:58:41.81 spid57 * Input Buffer 510 bytes -
2006-01-10 09:58:41.81 spid57 * CREATE ASSEMBLY Test FROM 0x4D5A90000300000004000000FFFF00
2006-01-10 09:58:41.81 spid57 * 00B800000000000000400000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F67
2006-01-10 09:58:41.82 spid57 * 72616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A240000
2006-01-10 09:58:41.82 spid57 * 0000000000504500004C01030093A4BE430000000000000000E0000E210B010800002000
2006-01-10 09:58:41.82 spid57 * 000020000000000000CE3300000020000000400000000040000020000000100000040000
2006-01-10 09:58:41.82 spid57 * 00000000000400000000000000008000000010000046FC00000300000400001000001000
2006-01-10 09:58:41.82 spid57 * 00000010000010000000000000100000000000000000000000803300004B000000004000
2006-01-10 09:58:41.82 spid57 * 00B803000000000000000000000000000000000000006000000C000000F43200001C0000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000200000080000000000000000000000082000004800000000000000000000
2006-01-10 09:58:41.82 spid57 * 002E74657874000000D41300000020000000200000001000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 00200000602E72737263000000B803000000400000001000000030000000000000000000
2006-01-10 09:58:41.82 spid57 * 0000000000400000402E72656C6F6300000C000000006000000010000000400000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000400000420000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 0000000000000000000000000000
2006-01-10 09:58:41.84 spid57 *
2006-01-10 09:58:41.84 spid57 *

|||

Can you email me the full log and the mini-dump (mdmp) file?

stevehem at microsoft dot com

Thanks

|||

Thanks for reporting this and for sending me the dump.

This is a known issue that should be fixed in an upcoming service pack. It only applies to UNSAFE and EXTERNAL_ACCESS assemblies in databases that have been created by one Login then detached and reattached by another Login.

The workaround is to run:

exec sp_changedbowner '<username of the db attacher, ‘sa’, or any windows authenticated login>'

|||Greetings...

I've same problem (CREATE ASSEMBLY fails with error "A severe error occurred on the current command.")...
And I'm trying to use Your solution, but I don't know what is "username of the db attacher"...
Can You help me?

Best regards...
|||

If using SQL authentication (you enter a username and password to connect to your server)

Code Snippet

EXEC sp_changedbown 'sa'

should be enough. If your not using SQL authentication, (you connect using Integrated Security=SSPI or "Windows Security") try:

Code Snippet

EXEC sp_changedbown 'BUILTIN\Administrators'

sql

Cannot deploy assembly from VS.NET into SQL server 2005

Hi,
when deploying assembly from VS into SQL Server 2005, deployment fails with these error messages:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

I'm deploying assembly with UNSAFE permission setting.
What's wrong, please?

Thanx, Fipil

Can you:

1. Post or describe your code?

2. See if the same error happens when you run CREATE ASSEMBLY, CREATE

FUNCTION etc. in Management Studio, rather than deploying through

Visual Studio?

3. Is this on SQL Server/VS RTM, or CTP builds? OS and processor architecture?

Thanks|||

1. Here is my code

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Cryptography;
using System.Security.Cryptography.Pkcs;
using System.Security.Cryptography.X509Certificates;
using System.Text;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetRandomPassword()
{
Random rnd=new Random();
string guid=Guid.NewGuid().ToString();
string pass = "";
for (int i = 0; i < 20; i++)
{
pass += guid[rnd.Next(guid.Length)];
}
return new SqlString(pass);
}

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBinary EncryptToken(string loginName, string password, byte[] bCert)
{
X509Certificate2 cert = new X509Certificate2(bCert);
string token = string.Format("{0};{1};{2}", loginName, password, cert.Thumbprint);
return new SqlBinary(encryptByCertPBK(cert,token));
}

private static byte[] encryptByCertPBK(X509Certificate2 cert, string msg)
{
Console.Write("Encrypting '{0}'... ", msg);
RSACryptoServiceProvider rsa = (RSACryptoServiceProvider)cert.PublicKey.Key;

byte[] textBytes = Encoding.UTF8.GetBytes(msg);
byte[] reth = rsa.Encrypt(textBytes, false);
Console.WriteLine("Done.");
return reth;

}

};

2. YES, the same error happens when I run CREATE ASSEMBLY in Management Studio:

CREATE ASSEMBLY DbTisSecurity
FROM 'D:\Test\DbFunctions.dll'
WITH PERMISSION_SET=UNSAFE;

3. I'm using the SQL Server 2005, Developer Edition, 32 bit, ver. 9.00.1399.06
OS: Win XP SP2, Czech version,
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional

|||

This seems to work fine for me. However, I don't happen to have the czech version of Windows XP handy to test this on.

Can you create and deploy other assemblies without seeing this error?

|||

When I create a new database, I can deploy this assembly without problem. But, when I'm working with this db, I'm creating and modifying db objects (tables, users, etc.), because of developing an application, after some time (a few days) the database becomes a descripted state. Suddenly I cannot deploy the assembly. I can deploy it with SAFE permission set, but I cannot deploy it with UNSAFE.

On SQL server logs I found this message:

SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Here is SQL DUMP:

2006-01-10 09:57:36.85 Server Error: 17310, Severity: 20, State: 1.
2006-01-10 09:57:36.85 Server A user request from the session with SPID 54 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
2006-01-10 09:58:41.79 spid57 Using 'dbghelp.dll' version '4.0.5'
2006-01-10 09:58:41.81 spid57 ***Stack Dump being sent to C:\...\LOG\SQLDump0025.txt
2006-01-10 09:58:41.81 spid57 SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2006-01-10 09:58:41.81 spid57 * *******************************************************************************
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * BEGIN STACK DUMP:
2006-01-10 09:58:41.81 spid57 * 01/10/06 09:58:41 spid 57
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * Exception Address = 01C364AC Module(sqlservr+00C364AC)
2006-01-10 09:58:41.81 spid57 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2006-01-10 09:58:41.81 spid57 * Access Violation occurred reading address 000000B4
2006-01-10 09:58:41.81 spid57 * Input Buffer 510 bytes -
2006-01-10 09:58:41.81 spid57 * CREATE ASSEMBLY Test FROM 0x4D5A90000300000004000000FFFF00
2006-01-10 09:58:41.81 spid57 * 00B800000000000000400000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F67
2006-01-10 09:58:41.82 spid57 * 72616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A240000
2006-01-10 09:58:41.82 spid57 * 0000000000504500004C01030093A4BE430000000000000000E0000E210B010800002000
2006-01-10 09:58:41.82 spid57 * 000020000000000000CE3300000020000000400000000040000020000000100000040000
2006-01-10 09:58:41.82 spid57 * 00000000000400000000000000008000000010000046FC00000300000400001000001000
2006-01-10 09:58:41.82 spid57 * 00000010000010000000000000100000000000000000000000803300004B000000004000
2006-01-10 09:58:41.82 spid57 * 00B803000000000000000000000000000000000000006000000C000000F43200001C0000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000200000080000000000000000000000082000004800000000000000000000
2006-01-10 09:58:41.82 spid57 * 002E74657874000000D41300000020000000200000001000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 00200000602E72737263000000B803000000400000001000000030000000000000000000
2006-01-10 09:58:41.82 spid57 * 0000000000400000402E72656C6F6300000C000000006000000010000000400000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000400000420000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 0000000000000000000000000000
2006-01-10 09:58:41.84 spid57 *
2006-01-10 09:58:41.84 spid57 *

|||

Can you email me the full log and the mini-dump (mdmp) file?

stevehem at microsoft dot com

Thanks

|||

Thanks for reporting this and for sending me the dump.

This is a known issue that should be fixed in an upcoming service pack. It only applies to UNSAFE and EXTERNAL_ACCESS assemblies in databases that have been created by one Login then detached and reattached by another Login.

The workaround is to run:

exec sp_changedbowner '<username of the db attacher, ‘sa’, or any windows authenticated login>'

|||Greetings...

I've same problem (CREATE ASSEMBLY fails with error "A severe error occurred on the current command.")...
And I'm trying to use Your solution, but I don't know what is "username of the db attacher"...
Can You help me?

Best regards...|||

If using SQL authentication (you enter a username and password to connect to your server)

Code Snippet

EXEC sp_changedbown 'sa'

should be enough. If your not using SQL authentication, (you connect using Integrated Security=SSPI or "Windows Security") try:

Code Snippet

EXEC sp_changedbown 'BUILTIN\Administrators'

Cannot deploy assembly from VS.NET into SQL server 2005

Hi,
when deploying assembly from VS into SQL Server 2005, deployment fails with these error messages:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

I'm deploying assembly with UNSAFE permission setting.
What's wrong, please?

Thanx, Fipil

Can you:
1. Post or describe your code?
2. See if the same error happens when you run CREATE ASSEMBLY, CREATE FUNCTION etc. in Management Studio, rather than deploying through Visual Studio?
3. Is this on SQL Server/VS RTM, or CTP builds? OS and processor architecture?
Thanks|||

1. Here is my code

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Cryptography;
using System.Security.Cryptography.Pkcs;
using System.Security.Cryptography.X509Certificates;
using System.Text;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetRandomPassword()
{
Random rnd=new Random();
string guid=Guid.NewGuid().ToString();
string pass = "";
for (int i = 0; i < 20; i++)
{
pass += guid[rnd.Next(guid.Length)];
}
return new SqlString(pass);
}

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBinary EncryptToken(string loginName, string password, byte[] bCert)
{
X509Certificate2 cert = new X509Certificate2(bCert);
string token = string.Format("{0};{1};{2}", loginName, password, cert.Thumbprint);
return new SqlBinary(encryptByCertPBK(cert,token));
}

private static byte[] encryptByCertPBK(X509Certificate2 cert, string msg)
{
Console.Write("Encrypting '{0}'... ", msg);
RSACryptoServiceProvider rsa = (RSACryptoServiceProvider)cert.PublicKey.Key;

byte[] textBytes = Encoding.UTF8.GetBytes(msg);
byte[] reth = rsa.Encrypt(textBytes, false);
Console.WriteLine("Done.");
return reth;

}

};

2. YES, the same error happens when I run CREATE ASSEMBLY in Management Studio:

CREATE ASSEMBLY DbTisSecurity
FROM 'D:\Test\DbFunctions.dll'
WITH PERMISSION_SET=UNSAFE;

3. I'm using the SQL Server 2005, Developer Edition, 32 bit, ver. 9.00.1399.06
OS: Win XP SP2, Czech version,
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional

|||

This seems to work fine for me. However, I don't happen to have the czech version of Windows XP handy to test this on.

Can you create and deploy other assemblies without seeing this error?

|||

When I create a new database, I can deploy this assembly without problem. But, when I'm working with this db, I'm creating and modifying db objects (tables, users, etc.), because of developing an application, after some time (a few days) the database becomes a descripted state. Suddenly I cannot deploy the assembly. I can deploy it with SAFE permission set, but I cannot deploy it with UNSAFE.

On SQL server logs I found this message:

SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Here is SQL DUMP:

2006-01-10 09:57:36.85 Server Error: 17310, Severity: 20, State: 1.
2006-01-10 09:57:36.85 Server A user request from the session with SPID 54 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
2006-01-10 09:58:41.79 spid57 Using 'dbghelp.dll' version '4.0.5'
2006-01-10 09:58:41.81 spid57 ***Stack Dump being sent to C:\...\LOG\SQLDump0025.txt
2006-01-10 09:58:41.81 spid57 SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2006-01-10 09:58:41.81 spid57 * *******************************************************************************
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * BEGIN STACK DUMP:
2006-01-10 09:58:41.81 spid57 * 01/10/06 09:58:41 spid 57
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * Exception Address = 01C364AC Module(sqlservr+00C364AC)
2006-01-10 09:58:41.81 spid57 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2006-01-10 09:58:41.81 spid57 * Access Violation occurred reading address 000000B4
2006-01-10 09:58:41.81 spid57 * Input Buffer 510 bytes -
2006-01-10 09:58:41.81 spid57 * CREATE ASSEMBLY Test FROM 0x4D5A90000300000004000000FFFF00
2006-01-10 09:58:41.81 spid57 * 00B800000000000000400000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F67
2006-01-10 09:58:41.82 spid57 * 72616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A240000
2006-01-10 09:58:41.82 spid57 * 0000000000504500004C01030093A4BE430000000000000000E0000E210B010800002000
2006-01-10 09:58:41.82 spid57 * 000020000000000000CE3300000020000000400000000040000020000000100000040000
2006-01-10 09:58:41.82 spid57 * 00000000000400000000000000008000000010000046FC00000300000400001000001000
2006-01-10 09:58:41.82 spid57 * 00000010000010000000000000100000000000000000000000803300004B000000004000
2006-01-10 09:58:41.82 spid57 * 00B803000000000000000000000000000000000000006000000C000000F43200001C0000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000200000080000000000000000000000082000004800000000000000000000
2006-01-10 09:58:41.82 spid57 * 002E74657874000000D41300000020000000200000001000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 00200000602E72737263000000B803000000400000001000000030000000000000000000
2006-01-10 09:58:41.82 spid57 * 0000000000400000402E72656C6F6300000C000000006000000010000000400000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000400000420000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 0000000000000000000000000000
2006-01-10 09:58:41.84 spid57 *
2006-01-10 09:58:41.84 spid57 *

|||

Can you email me the full log and the mini-dump (mdmp) file?

stevehem at microsoft dot com

Thanks

|||

Thanks for reporting this and for sending me the dump.

This is a known issue that should be fixed in an upcoming service pack. It only applies to UNSAFE and EXTERNAL_ACCESS assemblies in databases that have been created by one Login then detached and reattached by another Login.

The workaround is to run:

exec sp_changedbowner '<username of the db attacher, ‘sa’, or any windows authenticated login>'

|||Greetings...

I've same problem (CREATE ASSEMBLY fails with error "A severe error occurred on the current command.")...
And I'm trying to use Your solution, but I don't know what is "username of the db attacher"...
Can You help me?

Best regards...
|||

If using SQL authentication (you enter a username and password to connect to your server)

Code Snippet

EXEC sp_changedbown 'sa'

should be enough. If your not using SQL authentication, (you connect using Integrated Security=SSPI or "Windows Security") try:

Code Snippet

EXEC sp_changedbown 'BUILTIN\Administrators'

Cannot deploy assembly from VS.NET into SQL server 2005

Hi,
when deploying assembly from VS into SQL Server 2005, deployment fails with these error messages:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

I'm deploying assembly with UNSAFE permission setting.
What's wrong, please?

Thanx, Fipil

Can you:

1. Post or describe your code?

2. See if the same error happens when you run CREATE ASSEMBLY, CREATE

FUNCTION etc. in Management Studio, rather than deploying through

Visual Studio?

3. Is this on SQL Server/VS RTM, or CTP builds? OS and processor architecture?

Thanks|||

1. Here is my code

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Cryptography;
using System.Security.Cryptography.Pkcs;
using System.Security.Cryptography.X509Certificates;
using System.Text;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetRandomPassword()
{
Random rnd=new Random();
string guid=Guid.NewGuid().ToString();
string pass = "";
for (int i = 0; i < 20; i++)
{
pass += guid[rnd.Next(guid.Length)];
}
return new SqlString(pass);
}

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBinary EncryptToken(string loginName, string password, byte[] bCert)
{
X509Certificate2 cert = new X509Certificate2(bCert);
string token = string.Format("{0};{1};{2}", loginName, password, cert.Thumbprint);
return new SqlBinary(encryptByCertPBK(cert,token));
}

private static byte[] encryptByCertPBK(X509Certificate2 cert, string msg)
{
Console.Write("Encrypting '{0}'... ", msg);
RSACryptoServiceProvider rsa = (RSACryptoServiceProvider)cert.PublicKey.Key;

byte[] textBytes = Encoding.UTF8.GetBytes(msg);
byte[] reth = rsa.Encrypt(textBytes, false);
Console.WriteLine("Done.");
return reth;

}

};

2. YES, the same error happens when I run CREATE ASSEMBLY in Management Studio:

CREATE ASSEMBLY DbTisSecurity
FROM 'D:\Test\DbFunctions.dll'
WITH PERMISSION_SET=UNSAFE;

3. I'm using the SQL Server 2005, Developer Edition, 32 bit, ver. 9.00.1399.06
OS: Win XP SP2, Czech version,
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional

|||

This seems to work fine for me. However, I don't happen to have the czech version of Windows XP handy to test this on.

Can you create and deploy other assemblies without seeing this error?

|||

When I create a new database, I can deploy this assembly without problem. But, when I'm working with this db, I'm creating and modifying db objects (tables, users, etc.), because of developing an application, after some time (a few days) the database becomes a descripted state. Suddenly I cannot deploy the assembly. I can deploy it with SAFE permission set, but I cannot deploy it with UNSAFE.

On SQL server logs I found this message:

SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Here is SQL DUMP:

2006-01-10 09:57:36.85 Server Error: 17310, Severity: 20, State: 1.
2006-01-10 09:57:36.85 Server A user request from the session with SPID 54 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
2006-01-10 09:58:41.79 spid57 Using 'dbghelp.dll' version '4.0.5'
2006-01-10 09:58:41.81 spid57 ***Stack Dump being sent to C:\...\LOG\SQLDump0025.txt
2006-01-10 09:58:41.81 spid57 SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2006-01-10 09:58:41.81 spid57 * *******************************************************************************
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * BEGIN STACK DUMP:
2006-01-10 09:58:41.81 spid57 * 01/10/06 09:58:41 spid 57
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 *
2006-01-10 09:58:41.81 spid57 * Exception Address = 01C364AC Module(sqlservr+00C364AC)
2006-01-10 09:58:41.81 spid57 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2006-01-10 09:58:41.81 spid57 * Access Violation occurred reading address 000000B4
2006-01-10 09:58:41.81 spid57 * Input Buffer 510 bytes -
2006-01-10 09:58:41.81 spid57 * CREATE ASSEMBLY Test FROM 0x4D5A90000300000004000000FFFF00
2006-01-10 09:58:41.81 spid57 * 00B800000000000000400000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F67
2006-01-10 09:58:41.82 spid57 * 72616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A240000
2006-01-10 09:58:41.82 spid57 * 0000000000504500004C01030093A4BE430000000000000000E0000E210B010800002000
2006-01-10 09:58:41.82 spid57 * 000020000000000000CE3300000020000000400000000040000020000000100000040000
2006-01-10 09:58:41.82 spid57 * 00000000000400000000000000008000000010000046FC00000300000400001000001000
2006-01-10 09:58:41.82 spid57 * 00000010000010000000000000100000000000000000000000803300004B000000004000
2006-01-10 09:58:41.82 spid57 * 00B803000000000000000000000000000000000000006000000C000000F43200001C0000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000200000080000000000000000000000082000004800000000000000000000
2006-01-10 09:58:41.82 spid57 * 002E74657874000000D41300000020000000200000001000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 00200000602E72737263000000B803000000400000001000000030000000000000000000
2006-01-10 09:58:41.82 spid57 * 0000000000400000402E72656C6F6300000C000000006000000010000000400000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000400000420000000000000000000000000000000000000000000000
2006-01-10 09:58:41.82 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 000000000000000000000000000000000000000000000000000000000000000000000000
2006-01-10 09:58:41.84 spid57 * 0000000000000000000000000000
2006-01-10 09:58:41.84 spid57 *
2006-01-10 09:58:41.84 spid57 *

|||

Can you email me the full log and the mini-dump (mdmp) file?

stevehem at microsoft dot com

Thanks

|||

Thanks for reporting this and for sending me the dump.

This is a known issue that should be fixed in an upcoming service pack. It only applies to UNSAFE and EXTERNAL_ACCESS assemblies in databases that have been created by one Login then detached and reattached by another Login.

The workaround is to run:

exec sp_changedbowner '<username of the db attacher, ‘sa’, or any windows authenticated login>'

|||Greetings...

I've same problem (CREATE ASSEMBLY fails with error "A severe error occurred on the current command.")...
And I'm trying to use Your solution, but I don't know what is "username of the db attacher"...
Can You help me?

Best regards...|||

If using SQL authentication (you enter a username and password to connect to your server)

Code Snippet

EXEC sp_changedbown 'sa'

should be enough. If your not using SQL authentication, (you connect using Integrated Security=SSPI or "Windows Security") try:

Code Snippet

EXEC sp_changedbown 'BUILTIN\Administrators'

Cannot Create Assembly

I'm using SQL Server Management Studio Express and am unable to create an assembly. The following code:

create assembly HelloWorld from 'c:\HelloWorld.dll'

with permission_set = safe

returns the following error:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'assembly'.

I noticed that the word "assembly" isn't in blue text and what's more, if I expand the programmability node on any database, there is no assembly node. Anybody know what I'm doing wrong here?

Thanks in advance.

You are either not connected to a SQL Server 2005 instance or have the compability level to 80 or lower. (Which does not support assemblies / assembly keywords)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Thank you Jens. I'm pretty new to this, and compatability level is something I've never heard of before. Could you fill me in on how to change that or point me to a link. Much obliged.

Cannot CREATE ASSEMBLY

Hi,
I am new to SQL Server 2005 but not to dotnet development. I am trying to
use the CREATE ASSEMBLY on my machine that is running the developer edition.
The ASSEMBLY keyword is not highlighted as a recognised keyword and when I
run the SQL it fails (see below). Have I not enabled something or have I
just installed it incorrectly?
CREATE ASSEMBLY
helloworld FROM 'c:\helloworld.dll'
WITH PERMISSION_SET = SAFE
Regards,
Landers
Hello landers,
Some questions first:
a.) What error message do you actually get?
b.) What are you using for a T-SQL editor?
c.) If you run xp_msver, what output do you get?
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

Cannot CREATE ASSEMBLY

Hi,
I am new to SQL Server 2005 but not to dotnet development. I am trying to
use the CREATE ASSEMBLY on my machine that is running the developer edition.
The ASSEMBLY keyword is not highlighted as a recognised keyword and when I
run the SQL it fails (see below). Have I not enabled something or have I
just installed it incorrectly?
CREATE ASSEMBLY
helloworld FROM 'c:\helloworld.dll'
WITH PERMISSION_SET = SAFE
Regards,
LandersHello landers,
Some questions first:
a.) What error message do you actually get?
b.) What are you using for a T-SQL editor?
c.) If you run xp_msver, what output do you get?
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

Cannot CREATE ASSEMBLY

Hi,
I am new to SQL Server 2005 but not to dotnet development. I am trying to
use the CREATE ASSEMBLY on my machine that is running the developer edition.
The ASSEMBLY keyword is not highlighted as a recognised keyword and when I
run the SQL it fails (see below). Have I not enabled something or have I
just installed it incorrectly?
CREATE ASSEMBLY
helloworld FROM 'c:\helloworld.dll'
WITH PERMISSION_SET = SAFE
Regards,
LandersHello landers,
Some questions first:
a.) What error message do you actually get?
b.) What are you using for a T-SQL editor?
c.) If you run xp_msver, what output do you get?
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/