'Need more input, Stephanie!'

by Michael S. Kaplan, published on 2005/07/05 12:35 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2005/07/05/435709.aspx

Since these immortal words were spoken by the voice of Tim Blaney to Ally Sheedy, I think every one of us has at times felt like the industrious Number 5 who would read the encyclopedias and dictionaries if only you could point at the information, at the input.

I have talked about the New String Recommendations doc that Dave Fetterman reported on (full paper here). It inspires the same feeling in me, and in other people. More input is needed to make the choices about what methods to call, and under what circumstances. In the present form, there is too much that is unknown.

Now I did make the bold and somewhat audacious claim that you can summarize with use appropriate comparison methods and leave it at that. Now I will backpedal a little and show an example of when that can be difficult....

Let's take the seemingly simple (hah!) case of authentication in SQL Server. There are for our purposes usually three basic scenarios:

• SQL Authentication, where USERs and GROUPs are created within SQL Server. Validation of logons is done with SQL Server's collation mechanisms, using the SERVER collation setting (not the database). There are no specific functions one can call from Windows or the .NET Framework on a client machine to get identical results to those on the server (which is calling built-in, private versions of the Win32 CompreString and/or LCMapString functions). You can emulate the behavior for the most part by following the guidance in this post, assuming the client and the server are the same OS and service pack level.
  
• Windows Authentication, not using AD, where the NT symbolic idedntifier rules are used (the uppercase and then binary comparison that the .NET Framework emulates very effectively with its new in Whidbey OrdinalIgnoreCase semantics). This will usually be seen any time one is using a local user or group account defined on the machine, and could also be seen in larger domain scenarios if Active Directory is not being used.
  
• Windows Authentication, using AD, where the OS CompareString and/or LCMapString are used to authenticate, with most ignore flags passed, including NORM_IGNORECASE, NORM_IGNORENONSPACE, NORM_IGNOREWIDTH, and NORM_IGNOREKANA. This will be seen any time Active Directory gets involved in the picture. It is straightforward to emulate, assuming the client and the server are the same OS and service pack level.

There are other slight variations that can happen when other providers get involved in the OS security story. But let's leave those aside for the moment -- you can keep them in mind if these three alone do not scare you off. :-)

Anyway, given all of the above, how difficult do you think it would be to come up with a mechanism to emulate the authentication behavior of a SQL Server, for the purpose of providing a deny/grant mechanism for a database resource?

Pretty fiendishly difficult, I would say.

In all honesty, your best bet would be to always pass through to the server and let it decide who is authenticated. Not that navigating the above is impossible, but it is difficult to the point of being improbable, or at least impractial. Unless you are paid by the hour and are not required to show significant progress....

This post brought to you by "ƹ" (U+01b9, LATIN SMALL LETTER EZH REVERSED)

# Yaytay on 5 Jul 2005 1:29 PM:

# Michael S. Kaplan on 5 Jul 2005 1:38 PM:

referenced by

go to newer or older post, or back to index or month or day