SQL Server Project (2)

As mentioned in the links in the previous article, SQL Server Project provides some basics to connect to a SQL server and communicate with the server.

Here is a brief of these basics:

• Connect to SQL server by connection string "Context Connection=true" so that no need to specify database and authentication information for most cases, since the project is deployed to SQL debase and all the stored procedures, functions and others run in the context.


• Send messages back to SQL server by SqlContext.Pipe.Send() method. The method call has two overloads, one taking a string as its parameter, and another one IDataReader. To call this method, no connection is needed.


• Execute SQL statements through IDbCommand.ExecuteXXX() methods: ExecuteReader(), ExecuteNoneQuery(), and ExecuteScalar(). By using these methods, you can all SQL query and none query statements.

With all these basics, it looks that you can go ahead to create any SQL server database objects (stored procedure, function, triggers …). However, when I first time rolled my sleeves on a project, I encountered a problem. There was so limited number of references available in my Visual Studio project, about 5 only. There is no browse button for adding references neither. How can I use other Microsoft .Net libraries, as well as libraries I created?

To solve the problem, I finally realized that I have to understand the way how .Net assemblies are loaded and executed in a SQL server database. All the assemblies, including dependent assemblies, have to be registered in the server database. For example, if a stored procedure is defined in a .Net assembly, the assembly and its dependent assemblies have to be registered in a SQL server database. When the stored procedure is executed, the SQL server database will load the assembly and dependent ones from the database, not from local or remote file systems.

Therefore, if you want to add new references to your project, you have to register these libraries to the SQL server database first. The SQL server project’s reference dialog window lists only the libraries from the destination SQL server database.

To register a library to the SQL server database, you have to understand a few of additional concepts. First is the Permission set. To register an assembly, a Permission set has to be specified. There are three types: SAFE, EXTERNAL ACCESS, and UNSAFE. Try to register your libraries from SAFE mode first. If you get any error messages, you may have to try the next level till you can get them registered.

Permission sets are for security and reliability purposes. However, in most cases, you have no choice to use the least secured permission level. My understanding of those modes is that, if you run pure .Net managed codes and only for internal access, you should use SAFE mode. If you have to access to external resources such as file system, you have to use EXTERNAL ACCESS. If the libraries contain codes to access dynamical resources such as reflection load and web services or unmanaged codes, you have to no choice to UNSAFE. As a result, if you use some third party libraries, including many of Microsoft ones, you may end up with UNSAFE in most cases.

The second concept is that your assembly must be a strong named one if the permission mode is not SAFE. SQL server enforces this mandatory requirement for all none SAFE assemblies. If the assembly is not a strong named one, you have to sign it with a key by using the tool of sn.exe from Microsoft.

With these understandings, you are ready to register your dependency assemblies to a SQL server database. The followings are some SQL commands I used:

Add a key to assembly files

Signer.exe -k ..\..\myCompany_key.snk -outdir .\build -a *.dll

Add a strong name key and login permission for the key in SQL Server

Use master
IF EXISTS(SELECT * FROM sys.syslogins WHERE NAME = 'Assembly1_Login')
BEGIN
  DROP LOGIN Assembly1_Login;
END
IF EXISTS(SELECT * FROM sys.asymmetric_keys WHERE NAME = 'Assembly1_key')
BEGIN
  DROP ASYMMETRIC KEY Assembly1_key;
END
CREATE ASYMMETRIC KEY Assembly1_key FROM EXECUTABLE FILE = 'C:\Temp\bin\Assenbly1.dll';
CREATE LOGIN Assembly1_Login FROM ASYMMETRIC KEY Assembly1_key;
GRANT UNSAFE ASSEMBLY TO Assembly1_Login;

Register an assembly

DECLARE @asm VARCHAR(1024);
SET @asm = 'C:\Temp\bin\MyAssembly.dll';
IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'MyAssembly')
 DROP ASSEMBLY [MyAssembly]
CREATE ASSEMBLY [MyAssembly] FROM @asm WITH PERMISSION_SET = unsafe;

After your registration process, open your project in VS again. You should be able to add references to you project. I always save my registration process in a script. In case I have to reload my assemblies, or deploy the same one to another production database, I can use the script to do the job.