Monday, October 25, 2010

Forms Authentication against Active Directory

While trying to authenticate against AD I ran into some problems and was wondering if it was my ldap connection string.  Found a great way to verify it with the Group Policy Result tool (
gpresult.exe /r
It lists all sorts of items about the currently logged in user - look for the "User Settings" section, and the first line shows you the exact path to your entry.  In my case:

CN=John Hennesey,CN=Users,DC=domain,DC=com

and there is an entry "group policy was applied from" - there's your

and the web.config entries (no magic here - this is on thousands of web pages - this is for easy reference in the future)

      <add name="ADConnectionString" connectionString="LDAP://,DC=domain,DC=com"/>

<authentication mode="Forms">
      <forms name=".ADAuthCookie" timeout="10"/>
      <deny users="?"/>
<allow users="*"/>
<membership defaultProvider="MyADMembershipProvider">
      <add name="MyADMembershipProvider" type="System.Web.Security.ActiveDirectoryMembershipProvider, System.Web, Version=,         Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="ADConnectionString" attributeMapUsername="sAMAccountName"/>

Couple this with the asp Login control, and all set.  Easy!

Friday, October 22, 2010

Nolocks on a view - does it nolock all the tables in the view?

I received this question from a colleague this week:

"If there is a view that is not using nolock [in the from clause] but you have a stored procedure that uses nolock when it calls the view then do all the FROMs in the view run with nolock?"

The simple answer : Yes, the nolock hint on the view should propagate down to the tables queried in the view*.

A brief visit to our favorite resource, Books Online, reveals the following:
In SQL Server 2005/2008, all lock hints are propagated to all the tables and views that are referenced in a view. Also, SQL Server performs the corresponding lock consistency checks.

 And the asterisk:
*If a table contains computed columns and the computed columns are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables. This means the table hints are not propagated. For example, a NOLOCK table hint is specified on a table in the query. This table has computed columns that are computed by a combination of expressions and functions that access columns in another table. The tables referenced by the expressions and functions do not use the NOLOCK table hint when accessed.

And my (rather simple) test proved it out:

use master

create database lockTest

use lockTest

create table t1 (f1 int, f2 int)

insert into t1 values (1, 1)
insert into t1 values (2, 2)


create view v1 as
select *
from t1

set transaction isolation level serializable

begin tran
select * from v1

insert into v1 values (3,3)

-- go into a new window and run the following:
-- Works
select *
from v1 with (nolock)

-- Hangs
select *
from v1

-- cleanup
use master

drop database lockTest

Sql - search for column comments

Technical reference - an easy way to query the system tables to search for column comments.

select *
from sys.extended_properties
where class =
  and major_id = object_id(N'<<schema>>.<<table>>') 
  and name = 'MS_Description'

Reporting Services 2008 over SSL - Certificates

(If you are trying to run RS over SSL and want to cut to the chase, skip this paragraph of frustration)

Disclaimer – I am not that savvy with administration – better at the nuts & bolts of programming, so most of this was learning.

Today I needed to experiment with the LogonUser method of the web service – the only way to call it is over SSL.  OK, easy enough, just tell Reporting Services to use SSL – just a check box, right?  Nope.  Do a little reading – all signs point to HttpCfg.exe.  Unless using Vista.  Then it’s netsh.exe.  Need a certificate – must generate one.  Use MakeCert.  OK, got all my parameters in check – generated.  What’s the hash?  Use certutil to get the hash (thumbprint), guid (app id).  Register it with netsh http add …..   ok, everything all lined up.

Now let’s go back into Reporting Services Configuration Manager and instruct it to use the certificate.  Piece of cake, select it from the drop down and fire it up.  Error?  Certificate is not trusted?  Oh good gracious.  Keep on researching, find out just how easy it is to do in XP.  Nothing about how easy it is to do in Vista, just lots of people complaining about how incredibly hard it is.

Oh, what’s this?  IIS 7 has an easy way to do it?  Oh YEAH!  Here are the steps to quickly create a test certificate you can use seamlessly:

1. Open IIS 7, click on the computer you wish to create the certificate for.  In the Content View double click on Server Certificates.

2.    2. The top right has an “Actions” menu.  Click on “Create Certificate Request” – could it really be this easy?

3. Type in a name for your certificate (yes, it really is this easy)

4.  Launch Reporting Services Configuration Manager, go to “Web Service URL”, select your newly created SSL certificate from the list and click apply.  It is just that easy!

And now you can do all of your development testing over SSL locally.