Nick Grattan's Blog

About Microsoft SharePoint, .NET, Natural Language Processing and Machine Learning

Archive for the ‘Uncategorized’ Category

Encyrpting SQL Server Connections

leave a comment »

In SQL Server 2005 connections between client and server can be encrypted using SSL even if a X.509 certificate has not been installed on the server. When a certificate is not present SQL Server will automatically generate one.

By default, the certificate chain up to a root CA will be checked, and if a recognised root CA is not found, the connection will fail with the following exception message:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 31 – Encryption(ssl/tls) handshake failed)

You can request that the certificate check is not made by using the TrustServerCertificate connection string parameter. So, the full connection string to specify SSL encryption without checking the certificate is:

string connectionstring =
            “Server=(local);Database=AdventureWorks; Integrated Security=SSPI;Encrypt=true;TrustServerCertificate=true”;

It’s best to install a recognised certificate, but this approach is better than sending data in plain text across insecure networks.

Written by Nick Grattan

August 8, 2007 at 8:41 pm

Posted in Uncategorized

Bringing Impersonation to SQL Server 2005

with 6 comments

Frequently I find that I would like to execute TSQL commands under the context of another login, for example, when testing a database user’s permissions on objects. The SQL Server 2005 EXECUTE AS statement allows us to do this. To show how this works, first create a login and a user for the AdventureWorks database and test the current login and user name:


CREATE LOGIN OrdinaryLogin WITH PASSWORD = ‘Password’


CREATE USER OrdinaryUser FOR LOGIN OrdinaryLogin




This reports ‘W2003\Administrator’ and ‘dbo’ for me. Now, impersonate the login that’s just been created and test the current login and user name again:


EXECUTE AS LOGIN = ‘OrdinaryLogin’;



This now reports ‘OrdinaryLogin’ and ‘OrdinaryUser’. TSQL commands can now be executed in the security context of ‘OrdinaryUser’. Finally, you can revert back to the previous login by executing this statement:




When using EXECUTE AS the current user must have IMPERSONATE permissions on the target login; ‘dbo’ already has this permission for all database users but the permission can be granted to other users:




This now allows ‘OrdinaryUser’ to impersonate ‘AnotherUser’. SQL Server 2005 also provides the SETUSER to perform a similar task, but this can only be executed by sysadmin/db_owner roles members.

Written by Nick Grattan

July 24, 2007 at 7:30 am

Posted in Uncategorized

Embedding InfoPath Forms Service Forms in SharePoint

with 5 comments

This post has been replaced with this one! This provides a link to a comprehensive document describing this technique.

Question: When using InfoPath Forms with the InfoPath Forms Service the form displayed in the browser replaces the SharePoint Form from which it was launched. Is there a way of embedding the form in an SharePoint page so that users  will not close the browser once they’ve finished with the form and exit from the SharePoint application?

Answer: Yes! Here’s a brief description of the steps to do this. The “XmlFormView” web part can be used to host an InfoPath Server form on a Web Part page within a SharePoint application. This web part is located  in the assembly Microsoft.Office.InfoPath.Server.dll and the web part will need  to be hosted in your site by adding an entry in web.config’s “SafeControls” element.

Once this has been done you can publish an InfoPath template to a SharePoint library and make it available as the template for a form library. Then, create a Web Part page in your site and add an instance of the “XmlFormView” library. Configure this web part to reference the InfoPath template through the “XmlLocation” property.

Finally, you will need to add a “Submit” button on the form as you will need to save the form’s data once the user has completed the form.

Written by Nick Grattan

July 19, 2007 at 8:27 am

Posted in Uncategorized

Sandboxing an Assembly

leave a comment »

If your application is loading an assembly and then executing code within that assembly you may want to “sandbox” that assembly and run the assembly in the “Internet Zone”. This will control access to local resources, such as the file system and the registry. To do this, the assembly must be loaded into an AppDomain, and this has the additional advantage that the assembly can be unloaded once execution is completed.

The following ‘using’ statements will be required for these code examples:

using System.Security;

using System.Security.Policy;

using System.Security.Permissions;

First, you need to create an evidence object which specifies the “Internet Zone”:

object[] hostEvidence = { new Zone(SecurityZone.Internet) };

Evidence intEvidence = new Evidence(hostEvidence, null);

The AppDomain can now be created and the assembly loaded into the AppDomain:

AppDomain ad = AppDomain.CreateDomain(“AddIns”, null);


TestLib.TestLib remoteWorker = (TestLib.TestLib)




               false, // don’t ignore case

               0, // binding attributes

               null, // use default binder

               null, // args passed to constructor (none)

               null, // use culture from current thread  

               null, // no activation attributes

               intEvidence); //evidence

In this case the assembly is loaded from a file  called”TestLib.DLL”. By calling CreateInstanceFromAndUnwrap an instance of the class “TestLib” is created and through this instance methods in the class can be called:

double l;

l = remoteWorker.theMethod();

This assumes that the class TestLib has a method called ‘theMethod’ that takes no arguments and returns a double.

You might be tempted to apply the evidence to the AppDomain – the second parameter in CreateDomain is null in the code above but a reference to the evidence can be passed. However, when the AppDomain tries to load the assembly it will fail as the AppDomain has no access to the file system and so cannot load the assembly!

Written by Nick Grattan

July 13, 2007 at 1:53 pm

Posted in Uncategorized

Wierd use of C# ‘default’ keyword

with 3 comments

While looking through some Microsoft sample code I came across this:

public int amount = default(System.Int32);

The ‘default’ key word is normally used to initialise a member in a template class when you do not know what the type of the template class will be. For example, here’s an example from MSDN help:

public class GenericList
  private class Node
  public Node Next;
  public T Data;

  private Node head;
  public T GetNext()
      T temp = default(T);

The type ‘T’ is parameterised type for the template. With the line in bold the type ‘T’ could be a numeric value (so the variable should be initialised to ‘0’) or a reference (and so should be initialised to NULL). Using ‘default’ here makes sure the correct initialisation takes place.

Going back to the example from Microsoft, surely this should be just written as:

 public int amount = 0;

since there are no generics involved in this at all?

Written by Nick Grattan

June 27, 2007 at 8:24 pm

Posted in Uncategorized