SQL, Analysis Services & related stories.

How to avoid 1000 rows limitation when querying active directory (AD) from SQL 2005 with using custom code.

As all of you know it is possible to query active directory from SQL Server with using ADSI provider as linked server. This solution works fine until you will have a lot of users in active directory. According to best practice guide windows system engineers always configured AD to return no more then 1000 rows per one query. Of course you can avoid this limitation too (with using range keyword or some other ways, as for me require deep AD knowledge). Some other extremily complicated T-SQL scripts can be found in internet. My current post's goal is to show two main things:

1. how to register a couple of assemblies (your own and related system assemblies) in SQL Server with UNSAFE permission WITHOUT setting TRUSTWORTHY ON for your database according to best practice security guide. Security check will passed in this way according to certificate permissions.

2. how to create a simple CLR procedures for quirying AD as any other SQL datasource without creating linked server.

You can find MSADHelper2.rar project attached. It contains project files; you can extract MSADHelper.dll assembly from here, or build a new one by yourself (in that case please generate a new strong key, my is not included in project files).

P.S. I am not an expert in C#, so you can improve this source as you wish to add some dispose or other required methods.

Here is installation script:

SET NOCOUNT ON
GO
USE Master
GO

--sp_configure 'clr enabled', 1
--reconfigure with override

--ALTER DATABASE <your_database_name> SET TRUSTWORTHY OFF
--GO

-- create keys from assembly 

CREATE ASYMMETRIC KEY MSADHelperAsKey FROM EXECUTABLE FILE = 'C:\distrib\ADSI\MSADHelper2.dll' -- specify correct path here.
GO
CREATE ASYMMETRIC KEY SystemDirectoryServicesKey FROM EXECUTABLE FILE = 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll'
GO
--SELECT * FROM sys.asymmetric_keys
--GO

-- create logins for special goals
CREATE LOGIN SQLCLRHelperLogin FROM ASYMMETRIC KEY MSADHelperAsKey
GO
CREATE LOGIN SQLCLRSysDirServLogin FROM ASYMMETRIC KEY SystemDirectoryServicesKey
GO

-- grant necessary (UNSAFE) permissions
GRANT UNSAFE ASSEMBLY TO SQLCLRHelperLogin
GO
GRANT UNSAFE ASSEMBLY TO SQLCLRSysDirServLogin
GO

-----------------------------------------------------------------------
---
--- **************************************************************
---
-----------------------------------------------------------------------

USE <your_database_name>
GO

CREATE ASSEMBLY [System.DirectoryServices]
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll'
WITH PERMISSION_SET = UNSAFE
GO
CREATE ASSEMBLY [MsForClient.SqlServer.SqlClrToolkit.MSADHelper]
FROM 'C:\distrib\ADSI\MSADHelper2.dll' -- please specify correct path here
WITH PERMISSION_SET = UNSAFE
GO

--Have a look at the assembly within the database
--SELECT * FROM sys.assemblies
--SELECT * FROM sys.assembly_files
GO

--Create procedures from the assembly

-- return list of registered providers (from registry)
CREATE PROCEDURE dbo.usp_GetListOfRegisteredDirectoryProviders
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_GetListOfRegisteredDirectoryProviders
GO

-- test methods - is it possible to create AD object? 

CREATE PROCEDURE dbo.usp_TryAuthenticate(@pAdsiPath nvarchar(4000), @pSecure int)
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_TryAuthenticate
GO

-- by default any method will execute under SQL Server Service account. If this account don't have enought privileges - please specify another one as @pUserName and

-- @pPassword to access AD

CREATE PROCEDURE dbo.usp_TryAuthenticateAsUser(@pAdsiPath nvarchar(4000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int)
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_TryAuthenticateAsUser
GO

-- enum AD structure - be carefull, to much info!

CREATE PROCEDURE dbo.usp_FillInfoByPath(@pAdsiPath nvarchar(4000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int)
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_FillInfoByPath
GO

-- get AD object properties

CREATE PROCEDURE dbo.usp_GetNodeProperties(@pAdsiPath nvarchar(4000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int)
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_GetNodeProperties
GO

-- extract user list

CREATE PROCEDURE dbo.usp_GetUserList(@pAdsiPath nvarchar(4000), @pOutputFieldList NVarChar(2000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int, @pScope nvarchar(10))
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_GetUserList
GO

-- extract group list

CREATE PROCEDURE dbo.usp_GetGroupList(@pAdsiPath nvarchar(4000), @pOutputFieldList NVarChar(2000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int, @pScope nvarchar(10))
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_GetGroupList
GO

-- extract list of computers

CREATE PROCEDURE dbo.usp_GetComputerList(@pAdsiPath nvarchar(4000), @pOutputFieldList NVarChar(2000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int, @pScope nvarchar(10))
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_GetComputerList
GO

-- extract members of AD group

CREATE PROCEDURE dbo.usp_GetGroupMembers(@pAdsiPath nvarchar(4000), @pOutputFieldList NVarChar(2000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int)
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_GetGroupMembers
GO

-- enum user membership

CREATE PROCEDURE dbo.usp_GetUserMembership(@pAdsiPath nvarchar(4000), @pOutputFieldList NVarChar(2000), @pUserName sysname, @pPassword nvarchar(100), @pSecure int)
AS EXTERNAL NAME [MsForClient.SqlServer.SqlClrToolkit.MSADHelper].[MsForClient.SqlServer.SqlClrToolkit.MSADHelper].usp_GetUserMembership
GO

-- execute & test section

EXEC dbo.usp_GetListOfRegisteredDirectoryProviders
GO

-- try to check - if net framework carefully installed and AD objects created without any issue
DECLARE @pStr nvarchar(4000)
SET @pStr = N'LDAP://DC=HQ,DC=corp,DC=xxxxxxxx,DC=ru'
EXEC dbo.usp_TryAuthenticate @pStr, 1
GO
-- try to run under another account

DECLARE @pStr nvarchar(4000), @pUserName sysname, @pPassword nvarchar(100)
SELECT @pStr = N'LDAP://DC=HQ,DC=corp,DC=xxxxxxxx,DC=ru', @pUserName = N'SQLSvc', @pPassword = N'xxxxxxx'

EXEC dbo.usp_TryAuthenticateAsUser @pStr, @pUserName, @pPassword, 1
GO

-- extract AD info as relational tree (use ID & ParentID to build object tree)
DECLARE @pStr nvarchar(4000), @pUserName sysname, @pPassword nvarchar(100)
SET @pStr = N'LDAP://DC=HQ,DC=corp,DC=xxxxxxx,DC=ru'

EXEC dbo.usp_FillInfoByPath @pStr, @pUserName, @pPassword, 0
GO

--- get all properites of object
DECLARE @pStr nvarchar(4000), @pUserName sysname, @pPassword nvarchar(100)
--SET @pStr = N'LDAP://CN=User01,CN=Users,DC=HQ,DC=corp,DC=xxxxxxx,DC=ru'
SET @pStr = N'LDAP://CN=Domain Admins,CN=Users,DC=HQ,DC=corp,DC=xxxxxxxxxx,DC=ru'

EXEC dbo.usp_GetNodeProperties @pStr, @pUserName, @pPassword, 0
GO
---- get user list and attributes

DECLARE @pStr nvarchar(4000), @pOutputFieldList nvarchar(2000), @pUserName sysname, @pPassword nvarchar(100)
-- to extract users from exact OU
-- 'LDAP://OU=TestGroup,DC=HQ,DC=corp,DC=xxxxxxxx,DC=ru'
-- all users from AD
-- @pStr = N'LDAP://DC=HQ,DC=corp,DC=xxxxxxx,DC=ru'
SELECT @pStr = N'LDAP://DC=HQ,DC=corp,DC=xxxxxxxxx,DC=ru',
 @pOutputFieldList = 'cn,name,description,distinguishedName,whenChanged,primaryGroupID,memberOf', @pUserName = '', @pPassword = N''

EXEC dbo.usp_GetUserList @pStr, @pOutputFieldList, @pUserName, @pPassword, 0, 'Subtree'
GO

---- extract group list
DECLARE @pStr nvarchar(4000), @pOutputFieldList nvarchar(2000), @pUserName sysname, @pPassword nvarchar(100)
-- to extract groups from exact group
-- 'LDAP://CN=Domain Admins,CN=Users,DC=HQ,DC=corp,DC=xxxxxxx,DC=ru'
-- all groups from AD
-- @pStr = N'LDAP://DC=HQ,DC=corp,DC=xxxxxx,DC=ru'
SELECT @pStr = N'LDAP://DC=HQ,DC=corp,DC=xxxxxx,DC=ru',
 @pOutputFieldList = 'cn,name,description,distinguishedName,whenChanged', @pUserName = '', @pPassword = N''

EXEC dbo.usp_GetGroupList @pStr, @pOutputFieldList, @pUserName, @pPassword, 0, 'Subtree'
GO

---- get computer list
DECLARE @pStr nvarchar(4000), @pOutputFieldList nvarchar(2000), @pUserName sysname, @pPassword nvarchar(100)
SELECT @pStr = N'LDAP://DC=HQ,DC=corp,DC=xxxxxxxxx,DC=ru',
 @pOutputFieldList = 'cn,name,distinguishedName,whenChanged', @pUserName = '', @pPassword = N''

EXEC dbo.usp_GetComputerList @pStr, @pOutputFieldList, @pUserName, @pPassword, 0, 'Subtree'
GO

------- get group members
DECLARE @pStr nvarchar(4000), @pOutputFieldList nvarchar(2000), @pUserName sysname, @pPassword nvarchar(100)
--SELECT @pStr = N'LDAP://CN=Domain Admins,CN=Users,DC=HQ,DC=corp,DC=xxxxxxxxx,DC=ru',
-- @pOutputFieldList = 'sAMAccountName'
SELECT @pStr = N'LDAP://CN=Administrators,CN=Users,DC=HQ,DC=corp,DC=xxxxxxxxx,DC=ru',
 @pOutputFieldList = 'Name', @pUserName = '', @pPassword = N''

EXEC dbo.usp_GetGroupMembers @pStr, @pOutputFieldList, @pUserName, @pPassword, 0
GO

----- enlist user membership
DECLARE @pStr nvarchar(4000), @pOutputFieldList nvarchar(2000), @pUserName sysname, @pPassword nvarchar(100)
SELECT @pStr = N'LDAP://CN=Administrator,CN=Users,DC=HQ,DC=corp,DC=xxxxxxxxx,DC=ru',
 @pOutputFieldList = 'Name', @pUserName = '', @pPassword = N''

EXEC dbo.usp_GetUserMembership @pStr, @pOutputFieldList, @pUserName, @pPassword, 0
GO


-- Here is a section of cleanup script

USE <your_database_name>
GO

-- delete procedures
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetListOfRegisteredDirectoryProviders]') AND type in (N'P', N'PC'))
 DROP PROCEDURE [dbo].[usp_GetListOfRegisteredDirectoryProviders]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_TryAuthenticate]') AND type in (N'P', N'PC'))
 DROP PROCEDURE [dbo].[usp_TryAuthenticate]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_TryAuthenticateAsUser]') AND type in (N'P', N'PC'))
 DROP PROCEDURE [dbo].[usp_TryAuthenticateAsUser]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_FillInfoByPath]') AND type in (N'P', N'PC'))
 DROP PROCEDURE [dbo].[usp_FillInfoByPath]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetNodeProperties]') AND type in (N'P', N'PC'))
 DROP PROCEDURE [dbo].[usp_GetNodeProperties]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetUserList]') AND type in (N'P', N'PC'))
 DROP PROCEDURE [dbo].[usp_GetUserList]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetGroupList]') AND type in (N'P', N'PC'))
 DROP PROCEDURE [dbo].[usp_GetGroupList]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetComputerList]') AND type in (N'P', N'PC'))
 DROP PROCEDURE [dbo].[usp_GetComputerList]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetGroupMembers]') AND type in (N'P', N'PC'))
 DROP PROCEDURE [dbo].[usp_GetGroupMembers]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetUserMembership]') AND type in (N'P', N'PC'))
 DROP PROCEDURE [dbo].[usp_GetUserMembership]
GO

-- delete assemblies
IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'MsForClient.SqlServer.SqlClrToolkit.MSADHelper')
 DROP ASSEMBLY [MsForClient.SqlServer.SqlClrToolkit.MSADHelper]
GO

IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'System.DirectoryServices')
 DROP ASSEMBLY [System.DirectoryServices]
GO

USE Master
GO

-- drop logins

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SQLCLRSysDirServLogin')
 DROP LOGIN [SQLCLRSysDirServLogin]
GO

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SQLCLRHelperLogin')
 DROP LOGIN [SQLCLRHelperLogin]
GO

-- drop keys 

IF EXISTS(SELECT * FROM sys.asymmetric_keys WHERE [name] = 'MSADHelperAsKey')
 DROP ASYMMETRIC KEY MSADHelperAsKey
GO

IF EXISTS(SELECT * FROM sys.asymmetric_keys WHERE [name] = 'SystemDirectoryServicesKey')
 DROP ASYMMETRIC KEY SystemDirectoryServicesKey
GO

-- Seems to me finished :-)

Dec 08, 2007 :

i updated source code to fix bug - many thanks to Taylor Gerring

Published Thursday, March 22, 2007 3:08 PM by Igor Kovalenko
Attachment(s): MSADHelper2.rar

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Euan Garden's BLOG said:

There are several ways but the most commonly used approach of using linked servers hits ona 1000 row

March 22, 2007 3:52 PM
 

Mark Reen said:

I have been trying to use your process to query AD. When I first loaded everthing, It work fine. Then

after about 4 hours it all stopped working. I am getting the error below. Any ideas on how to fix this issue?

Mark R.

Msg 6522, Level 16, State 1, Procedure usp_TryAuthenticate, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "usp_TryAuthenticate":

System.Runtime.InteropServices.COMException: The server is not operational.

System.Runtime.InteropServices.COMException:

  at MsForClient.SqlServer.SqlClrToolkit.MSADHelper.usp_TryAuthenticate(String ProviderString, Int32 Secure)

April 25, 2007 1:29 PM
 

Igor Kovalenko said:

Mark,

this may only meen that your domain controller (AD) is unavailable ("Server is not operational"). Please check for network issues or possible AD issues. Buy the way tomorrow i am planning to make a deep test in special environment under pressure to reproduce the behavior to fall into situation as you described.

April 25, 2007 3:04 PM
 

Des Jacobsen said:

Can I sugest you need one additional step in this procedure.

Before you try to create the ASYMMETRIC KEYmake sure that the SQL Server service account has permision to the directory that your assemply is stored in. If you dont you get an error.

The certificate, asymetric key, or private key file does not exist or has invalid format.

May 25, 2007 2:00 AM
 

Jian said:

Thanks for this artical.  I am wondering if there is a way to get the AzMan store in AD or even directly authorize the user in Stored Proc.

Thanks in advance!

Jian

May 31, 2007 3:04 PM
 

Igor Kovalenko said:

I may miss something but as for me it will be difficult to directly authorize users in stored proc. such kind of stored procedures. By default it works under security context of sql server account.

You may try to use another approach. Firstly, get calling user windows loginname. Second. extract the members of the group which authorized user should belong to. the second, enum all members of the group to check if user is a member of the group.

Something like this

CREATE PROCEDURE usp_before_calling_AD

 @param1, @param2, @param 3 etc

AS

-- get original caller

DECLARE @usr as sysname

SET @usr = ORIGINAL_LOGIN()

-- than extract your secret group members from AD:

CREATE TABLE #temp (

   cn sysname )

SET @pStr = N'LDAP://DC=HQ,DC=corp,DC=xxxxxxxxx,DC=ru'

SET @pOutputFieldList = 'cn'

INSERT INTO #temp

EXEC dbo.usp_GetUserList @pStr, @pOutputFieldList, NULL, NULL, 0, 'Subtree'

-- and last step

IF EXISTS(SELECT TOP 1 1 FROM #temp whete cn = @usr)

     SELECT 'allowed user'

ELSE

     SELECT 'deny logon'

RETURN

May 31, 2007 3:29 PM
 

davidn@magenic.com said:

Hi Igor,

I was able to use your toolkit almost 100% outofthebox.  I do have a question.  My company required the all the LDAP paths to be passed store in a config file...How I make it read AD info from a MyAssembly.dll.config?

Where is the physical location that I need to deploy my config file if my dll/config are residing in this structure

C:\MyApp\Bin\Debug\MyAssembly.dll

C:\MyApp\Bin\Debug\MyAssembly.dll.config

Thanks,

David.

DavidN@magenic.com

September 21, 2007 11:13 AM
 

Igor Kovalenko said:

Truly says i have no idea how to deploy this assembly with config file into SQL. Anyway you can try to use the following:

- change source code and drop all parameters because of unnecessary.

- define module internal variables (like former parameters)

- to initiate parameters

    - open C:\MyApp\Bin\Debug\MyAssembly.dll.config as simple xml file (with using System.XML XmlReader or XmlTextReader)

    - parse config file to extract necessary parameter's value

- call AD when ready.

In this case you'll call EXEC dbo.usp_GetUserList without parameters. Even more you can impersonate callers within function to read the different config files. The only check for appropriate permissions for folder C:\MyApp\Bin\Debug\.

September 23, 2007 3:02 PM
 

Scott Marquardt said:

Very nice indeed.

Has anyone cobbled together a similar attribute-writable toolkit?

October 4, 2007 2:29 PM
 

Scott Marquardt said:

Nuts.

I'm finding a problem with this, and I can't identify a culprit in the code.

I have some schema extensions. When I run the user list and the attributes are returned, one of these custom attributes returns fine, until it comes to users for whom the attribute is a null. At that point, the populated attribute of the immediately prior user ends up being returned for all subsequent users for whom the attribute is a null. It's as if there's a variable somewhere that's not getting reset, in the code. But I can't find anything like that at all in the code.

When the results get to another user with a populated attribute, things return to normal until another user with that attribute null comes along -- then that user and any subsequent users with that attribute null "inherit" the previous user's attribute.

OK, more info. It does this with any attribute, not just custom attributes. I figured as much, but wanted to check.

Could it be lines 651 to 657 in ADInfo?

October 9, 2007 9:18 PM
 

gjohnson said:

Scott did you find a solution to the null issue?  

October 30, 2007 2:05 PM
 

Scott Marquardt said:

No, alas. No joy. In fact, I nearly forgot about the bug until just this moment, when I returned to the method and was puzzled by something. Which led me back here in hopes that someone had resolved the bug, only to find that others wonder whether *I* had.   ;-)

Darn the luck for us both!

November 1, 2007 5:55 PM
 

Scott Marquardt said:

I still haven't figured out why the code is doing this, though I'm beginning to suspect it ain't the code.   (!)

November 6, 2007 11:59 AM
 

Taylor Gerring said:

Scott, I think I have a solution to your problem! Around line 656 in ADInfo.cs you will see something like:

//start code

if (PropertyValues.Length > 0)

   record.SetSqlString(Count + 1, PropertyValues.Substring(0, PropertyValues.Length - 1));

//end code

Add the below ELSE to it, so the whole block looks like below:

//start code

if (PropertyValues.Length > 0)

   record.SetSqlString(Count + 1, PropertyValues.Substring(0, PropertyValues.Length - 1));

else

   record.SetSqlString(Count + 1, SqlString.Null);

//end code

Re-build the DDL, run an ALTER ASSEMBLY to register the new DLL with SQL Server, and try your problematic query again. Instead of getting duplicate rows, this change now gives me a SQL NULL, as one would expect.

December 5, 2007 4:00 PM
 

Claudia said:

This works very well, but I still have one Problem: I want to read also the Object ID which is a Byte Array in AD. How can I convert this in a varchar Format?

Thank you for your help!

December 10, 2007 7:39 AM
 

Igor Kovalenko said:

Claudia, I have no idea. I only know how to convert BigInt (like LastLogon) to date.

December 10, 2007 3:22 PM
 

Scott Marquardt said:

Taylor, that's great. Worked perfectly. Thanks so much!

December 11, 2007 4:55 PM
 

BJ said:

(5 row(s) affected)

Msg 6522, Level 16, State 1, Procedure usp_TryAuthenticate, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'usp_TryAuthenticate':

System.DirectoryServices.DirectoryServicesCOMException: A referral was returned from the server.

System.DirectoryServices.DirectoryServicesCOMException:

  at MsForClient.SqlServer.SqlClrToolkit.MSADHelper.usp_TryAuthenticate(String ProviderString, Int32 Secure)

.

I am getting this when I tried to run the section that follows to test:

EXEC dbo.usp_GetListOfRegisteredDirectoryProviders

GO

-- try to check - if net framework carefully installed and AD objects created without any issue

DECLARE @pStr nvarchar(4000)

SET @pStr = N'LDAP://DC=HQ,DC=corp,DC=wmdomain.local,DC=ru'

EXEC dbo.usp_TryAuthenticate @pStr, 1

GO

Any ideas?

January 14, 2008 10:38 AM
 

Scott Marquardt said:

Whokay, here's a new problem:

http://www.sqlskills.com/blogs/bobb/post/How-do-you-shutdown-a-running-SQLCLR-appdomain.aspx

Thus:

>[Error] Script lines: 1-41 -------------------------

A .NET Framework error occurred during execution of user-defined routine or aggregate "usp_GetUserList":

System.IO.FileLoadException: Could not load file or assembly 'System.DirectoryServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)

System.IO.FileLoadException:

  at MsForClient.SqlServer.SqlClrToolkit.MSADHelper.usp_GetUserList(String ProviderStringPath, String OutputFieldList, String UserName, String Password, Int32 Secure, String Scope)

Any ideas on fixing this? Alter Assembly doesn't seem to do the trick. Total dropping and recreation doesn't, either.

Is there an approved assembly that could be referenced without code changes?

October 23, 2008 1:36 PM
 

Igor Kovalenko said:

Scott, i am so sorry, on my mind something wrong EITHER with Net Framework OR with assembly's strong key. As for me the best choice will be to drop assembly from SQL Server & GAC, rebuild source code locally, sign it with strong key, and register assembly to SQL Server again. GAC is not necessary. Now i am not an MSFT; have no chance to install Visual Studio to debug issues.

Sorry again.

October 23, 2008 1:52 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About Igor Kovalenko

I've been in IT since 1991 starting my carrier on Unix & C development. Now i am a consultant in Microsoft Services, Russia. My areas of experience - SQL & OLAP. I've been working with Microsoft tools for more than 15 years, started from asm 5.0 and Quick C 2.51 through (Visual) FoxPro, VB, C#... But my mission is SQL. Truly says i have enough knowledge both Oracle (8, 9.i) and Microsoft db technology, but it's to hard for me to cover both :-). My real data warehousing experience started with one of the largest DW implementation with using Oracle 9i in Russia till 2002. Of course i also implemented the first part of BI project on top of this DW with using SQL AS 2000 & Crystal reports. After that for a year i was a seniour developer, Online Services, in Dell UK, Bracknell (c++/vb/Oracle/SQL/ASP). In 2003 i was a little bit tired from High Technology World and decided to join Deloitte, Moscow, where i was a Finance analyst, member of Business Director Group. I really miss a half of my IT knowledge this time (SQL & Crystal is only useful), but now i perfectly know the "underground" of any BIG 4 consulting company, budgeting and managing process details, FTE, Utilization, OPTS analysis.... Hell, real accounting hell. I was excited to design and implement my first (and last) project with using Cognos EP tool. At the end of 2005 i was hired by my favorite company :-) Microsoft and now i am working with my favorite tool: SQL Server. To keep a long story short :-).

This Blog

Syndication

News

Locations of visitors to this page

© 2009 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Microsoft
Page view tracker