• BASIC - Can ONLY be used with SSL, and should be reserved for the last resort
• DIGEST - Basically a one-way MD5 hash that ONLY works for Domain Accounts and then ONLY if they are native Windows Server 2003. (Local accounts will not work)
• NTLM - Good ole SSPI Challenge/Response. This is the default authentication method used for Integrated Security unless you've setup Kerberos to work with your SQL environment
• Kerberos - Pretty much the standard Internet authentication method of choice these days, but not available by default for use with SQL Server endpoints.
• Integrated - Integrated security will use either NTLM or Kerberos depending on how the client requests authentication, and depending on what is available.

If you want the most secure connection, you'll want to use Kerberos, but as noted above, it is not configured by default, and must be setup using tools from the Windows 2003 Support tools add-on. 

I'll write more in a follow on article on just exactly how to configure Kerberos and http endpoints.

IAsyncResult aResult = command.BeginExecuteReader();

.. Do something

.. Do more things

SqlDataReader reader = command.EndExecuteReader(aResult);

1. Identify exactly what you want to accomplish, and write stored procedures that accomplish that. (You don't have to have stored procs, but for the sake of simplicity, lets pretend that you do)
2. Create an http endpoint, which defines a service and webmethod
3. Create a client to consume the service you created in 2.

It sounds pretty simple, and in reality, it is.. (Of course there's a lot to think about in terms of exactly how the service is created and accessed, and there are a LOT of application and security implications to consider as well)

Here's an example of the TSQL code necessary to create an Endpoint:

CREATE endpoint RMSSug

state

The above code snippet creates a service called RMSSug that is available on "localhost" accessible through the URL "http://localhost/OrdersRange", using integrated authentication and running on port 80. This Service has a method named "SalesRate", which executes a stored procedure in the AdventureWorks database called "uspOrdersRange", which takes no parameters and returns a recordset. (It also enables SQL Batches, which will be discussed in a future blog entry)

Once the service is created, you can create a client to access the web service. Here is an example of how you'd access the webmethod from a Windows Form application. Note that the type returned from the call to the webservice is of type object[] and must be CAST to an appropriate object (in my case a DataSet which is then bound to a DataGridView control on the form)

In this case there are a couple of things to point out. First off, note the call to the WebService happens after I've attached the my credentials (See the line following the setup of the WebService).. Secondly, note that I am creating a DataSet by CASTing the first object returned to a DataSet..  Also note that I didn't  really need to explicitly define the DataTable, but wanted to do that to show how it's all put together.

• MARS (Multiple Active Result Sets) - MARS is on by default when you install the SQL Native Client (SNAC), which also replaces your SQLOLEDB drivers.. You need to be careful with this feature, because it can lead to some very interesting bad programming practices.
• Async Queries - Not a feature of SQL Server, but rather of the SNAC client... This is a very nice parallelization option that can really increase your performance if used correctly.
• Query Notification - Using the Service Broker infrastructure, this is some pretty nice stuff, however the behind the scenes code generation that SQL Server goes through to implement this means that you really need to watch how you use this feature.
• Snapshot Isolation - Be prepared to really watch TempDB when you use this feature...
• http Endpoints - Some nice stuff can be accomplished here, but you need to be careful when returning a recordset, as the .NET framework 2.0 will return these as an object[] array, which means that you need to convert them to something useful within your code. You'll need to pay close attention to how you approach these.
• Table Partitions - Implemented with some strange syntax, this feature can really help you in the event that you want to build some highly performant systems. Be careful though, because the overhead of letting SQL Server put your tables back together can get interesting.

All in all, I think the session went pretty well. (We spent just over 2 hours going over these issues) My colleagues who were present had some good critisism of the presentation, but I think overall the people who attended were pretty happy with it..