LDAP - HOW DO I CONNECT TO LDAP DB FROM SQL SERVER 2005?
Dec 12, 2006
This is something that should not be so difficult, but it is proving to be very cumbersome.
I need to query an LDAP database on a Linux server FROM a sql server, namely sql server 2005.
I have found bits and pieces, but can€™t seem to put it all together yet €“ it should be easy, but it€™s proving to be very difficult.
It seems like you can either link an LDAP server through sp_addlinkedserver OR through enterprise manager/sql server studio, you can create a LINKED SERVER.
The problem is HOW to do this.
Here are the parameters for the ldap server:
Server name: serverabc
Ldap info (which I don€™t know how to label) is: 'ou=Hosts,dc=mc,dc=vanderbilt,dc=edu'.
I was able to somehow get this to work last week, but now it€™s not working - it didn't return any data, as i don't have any ldap hierarchy specified, but at least it came back with "command completed successfully":
SELECT * FROM OPENQUERY( [serverabc],
'SELECT * FROM ''LDAP://serverabcu/ OU=Hosts,DC=mc,DC=vanderbilt,DC=edu''
')
With all the ldap usage out there, this is really frustrating not being able to find out how to do this.
Any help is greatly appreciated.
Thanks
I have very unusual question. I have seen a query that looks something like this:
SELECT A, B, C FROM 'LDAP://abc.def.com'
What exactly does this mean? Could such a query be executed in the SQL Server Management Studio? Where would such a table/database be actually located?
Hello I am trying to run a query via tsql against ad. Below is the error I am getting. I have read the http://msdn2.microsoft.com/en-US/library/ms190803.aspx and changed the domain but still having issues. Any help would be appreciated.
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
GO
SELECT *
FROM OPENQUERY( ADSI,
'SELECT Name, SN, ST
FROM ''LDAP://ADSISrv/ OU=Users,DC=XXXXX,DC=LOCAL''
WHERE objectCategory = ''Person'' AND
objectClass = ''user''')
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT Name, SN, ST
FROM 'LDAP://ADSISrv/ OU=Users,DC=XXXXX,DC=LOCAL'
WHERE objectCategory = 'Person' AND
objectClass = 'user'" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
Hi, can anyone tell me how exactly sql server7 interacts with Site Server, LDAP. I am a sql dba, trying to work with above ones. Anyone pls suggest me any book or material or ur ideas.
I thought I was successful in linking SQL to a Third Party LDAP in the past, but I cannot get it to work now. It has been about 2 years ago I played with this. The LDAP does require authentication and I think that is where the problem lies, but if anyone can tell me if there is a way to do this and what the steps are I would greatly appreciate it.
We have a custom application that connects remotely to a SQL Server 2000 (SP2) database. We would like our application to validate a user's login against Active Directory.
So far I have been able to get a lookup working, but I can not find documentation on how to validate the password from within SQL Server. I found a lot of notes on using ASP.NET objects, or VB, C#, etc, but for this solution it must be done entirely in SQL. It would also be nice if this were SQL Server 2005; we could just embed the C# code and call it as a SQL stored proc, but unfortunately we are left with the constraint that we cannot upgrade this DB at this time.
Linked server 'ADSI' is set up with the sp_addlinkedserver command:
Two table functions in our test DB (trying to test both ways I've found in docs):
ALTER FUNCTION [dbo].[GetAuthenticatedUserViaLDAP] ( -- Add the parameters for the function here @userId nvarchar(50), @password nvarchar(50) ) RETURNS TABLE AS RETURN ( -- Add the SELECT statement with parameter references here SELECT [SAMAccountName], CN [Name], SN [Last Name], ST State FROM OPENQUERY( ADSI, '<LDAP://DC=company,DC=com>;((objectClass=user));SAMAccountName,cn,sn,st') WHERE [SAMAccountName] = @userId )
ALTER FUNCTION [dbo].[GetAuthenticatedUser] ( -- Add the parameters for the function here @userId nvarchar(50), @password nvarchar(50) ) RETURNS TABLE AS RETURN ( -- Add the SELECT statement with parameter references here SELECT [SAMAccountName], [Name], SN [Last Name], ST State FROM OPENQUERY( ADSI, 'SELECT SAMAccountName, Name, SN, ST FROM ''LDAP://bdsserver1/ CN=users,DC=company,DC=com'' WHERE objectCategory = ''Person'' AND objectClass = ''user'' ') WHERE [SAMAccountName] = @userId )
So calling either of these table functions from our custom application gives the same result:
select * from dbo.GetAuthenticatedUser('astonaker','abc') OR select * from dbo.GetAuthenticatedUserViaLDAP('astonaker','abc')
ResultSet:
'astonaker', 'Anthony', 'Stonaker' 'NULL'
So I can at least tell if a given user exists or not, but I have no visibility into whether the password they entered into our application is valid in LDAP.
I don't want to pass unencrypted passwords through the network, but then I don't know how to encrypt/compare these passwords without using the .NET Connection or DirectoryEntry, etc objects.
Is there a way to query the LDAP from inside the CMS? I know I can add a linked server in a singular instance but I'd like to do it inside a server group. I haven't found anything so far about querying the LDAP inside a server group so it might not be possible?
Hi,I have successfully set and used a linked server to query ADSI.Since this question also concerns MSSQLServer, I've cross posted it --I hope this is not a breach of etiquette.I have successly created a view based on the linked server.Unfortunately, it only shows 1000 records, and there does not seem tobe any way to set the Page Size.I found the following:http://support.microsoft.com/defaul...kb;en-us;243281Which seems to imply that the default can be set by changing registrykey: "HKEY_CURRENT_USERSoftwarePoliciesMicrosoftWind owsDirectoryUI"I have set this key, and also set it for the user account under whichMSSQLServer runs. The value persists after a reboot. The Domain Grouppolicy sets the default to 15000.This behaviour is not restricted to the linked server. If I use thescript found here:http://hacks.oreilly.com/pub/h/1121 I can access morethan 1000 records, but only if I set the "Page Size" property. If Icomment it out to let the default hold, it is 1000.It must be settable SOMEWHERE or the whole linked server thing is ofvery limited use.At present, the best solution I've been able to come up with is to usethe above script modified to run as a DTS package. Yuck.TIA,BM
Dear Friends, Here I have 2 stored procedure to return data via LDAP from Active Directory. The first SP it's OK, but the second doesn't. The difference is in ''@MyDIR'' because in the second I want to use a paramter... How can I use a paramter in the second SP? THANKS
1. CREATE PROCEDURE [dbo].[GD_SP_LDAP_CGD_GET_CNsByDIR] @MyDIR nvarchar(20) AS
DECLARE @SQLString NVARCHAR(500); SET @MyDIR ='DSO'
SET @SQLString = N'SELECT * FROM OPENQUERY(ADSI,'' SELECT name FROM ''''LDAP://OU=DSO,OU=Estacoes,OU=Servicos-Centrais,OU=cgd,DC=GrupoCGD,DC=com'''' '')';
EXECUTE sp_executesql @SQLString;
2. CREATE PROCEDURE [dbo].[GD_SP_LDAP_CGD_GET_CNsByDIR] @MyDIR nvarchar(20) AS
DECLARE @SQLString NVARCHAR(500); SET @MyDIR ='DSO'
SET @SQLString = N'SELECT * FROM OPENQUERY(ADSI,'' SELECT name FROM ''''LDAP://OU='' @MyDIR '' ,OU=Estacoes,OU=Servicos-Centrais,OU=cgd,DC=GrupoCGD,DC=com'''' '')';
I need to export data from an LDAP-compliant data source, to a SQL Server database. Is this possible with SQL Server 2005 Integration Services? The Import and Export Wizard does not appear to support LDAP in the Data Source drop down list.
I need to export data from an LDAP-compliant data source, to a SQL Server database. Is this possible with SQL Server 2005 Integration Services? The Import and Export Wizard does not appear to support LDAP in the Data Source drop down list.
Hi,- SQLserver 2000- Yellowfin 2.4- Windows 2003 serverI have been asked to investigate seting up LDAP authentication toaccess ourYellowfin reporting.If anyone has experience in setting this up? Your help would beappreciated.Tips and tricks .. ??ThanksDuncan Beaumont
Dear friens, I'm having a problem with ADSI and SQL... Could you tell me, where is the error? The problem surge when I try to use parameter€¦
" CREATE PROCEDURE TEST AS DECLARE @charVariable nvarchar(11); DECLARE @SQLString NVARCHAR(500); DECLARE @ParmDefinition NVARCHAR(500);
/* Build the SQL string one time. */ SET @SQLString = N'SELECT * FROM OPENQUERY(ADSI,''SELECT * FROM ''''LDAP://DC=GrupoCGD,DC=com'''' WHERE objectClass = ''''user'''' AND sAMAccountName=@UserID'')'
/* Specify the parameter format one time. */ SET @ParmDefinition = N'@UserID varchar(11)';
/* Execute the string with the first parameter value. */ SET @charVariable = 'rhs0002'; EXECUTE sp_executesql @SQLString, @ParmDefinition, @userID = @charVariable; "
ERROR is: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "SELECT * FROM 'LDAP://DC=GrupoCGD,DC=com' WHERE objectClass = 'user' AND sAMAccountName=@UserID" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
FOR EXAMPLE THIS VIEW WORKS€¦ USE [dbGestaoDesktop] GO /****** Object: View [dbo].[AD_VW_DIR_Users] Script Date: 11/22/2006 11:53:13 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE VIEW [dbo].[AD_VW_DIR_Users] AS SELECT TOP (100) PERCENT cn, createTimeStamp,department, extensionAttribute1 FROM OPENQUERY(ADSI, ' SELECT createTimeStamp,cn, department, extensionAttribute1 FROM ''LDAP://OU=DSO,OU=Utilizadores,OU=Servicos-Centrais,OU=cgd,DC=GrupoCGD,DC=com''
WHERE objectclass=''user''
') AS MyTable WHERE (NOT (cn IS NULL)) ORDER BY createTimeStamp
I have a linked server set up and working correctly. I can create a query to get all the users from active directory with something like this:
SELECT [name], [samaccountname] from OpenQuery( ADSI, 'SELECT name, samaccountname FROM ''LDAP://DC=domain,DC=com'' WHERE objectClass = ''user'' and objectCategory=''Person''')
Now I am trying to select all the users in a specifed security group, but I am not having much luck. What is the best way to get this?
I'm trying to query an LDAP server from a stored procedure written for the CLR but not getting the expected results.
The code is as follows:
<Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub LDAP_UserExists(<Out()> ByRef exists As Boolean, ByVal username As SqlString)
Dim adspath As New StringBuilder() adspath.Append(LDAP://[.......]/ou=Members/cn=) adspath.Append(username)
If username.ToString().Length > 0 Then Dim uobject As New DirectoryEntry(adspath.ToString(), "", "", System.DirectoryServices.AuthenticationTypes.Anonymous) If Not (uobject Is Nothing) Then exists = True Else exists = False End If End If
End Sub
The same code works fine from an ASP.NET. If I deploy the code and execute it with
exec LDAP_UserExists 'username'
I receive the error
Error converting data type varchar to bit.
And if I right-click and select "Execute Stored Procedure..." I receive @exists = 1 and Return Value = 0, regardless of the value I pass in as the username parameter.
Given that the same code works correctly on the ASP.NET page I suspect that this error has something to do with the <out()> parameter in the stored procedure declaration.
Can anyone suggest the correct method of performing this query?
I currently have the problem that I have to write some data into a SUN Directory Server 5.2 LDAP directory. Does anyone know how I can do this. I already found some articles in this forum that provide solutions how to access the active directory - but how can I access a none Microsoft LDAP Server?
Is there any way to use the OLE DB Destination or do I have to implement my own LDAP adapter in VB.net?
I am trying to get members of an Active Directory group by querying the AD server from Transact-SQL (SQL Server 2005). Although there does not seem to be any written list of LDAP attributes that can be queried in AD (or I am not finding it), I have gotten this far:
SELECT * FROM
OPENQUERY( MYSERVER,
'SELECT cn, msExchHomeServerName, userPrincipalName FROM ''LDAP://CN=Users,DC=MYSERVER,DC=COM'' WHERE userPrincipalName=''*'' ')
This gives me a user list. But I can't find the syntax or attribute name(s) to query in order to get the membership of a specific group - for example, the group "SQL_Developers".
Anybody out there familiar enough with LDAP, AD and OPENQUERY() to give me a hand?
Is it possible to use a parameter in a ldap query using the ADsDSOObject provider? I keep getting an error "The ICommandWithParameters interface is not supported by the "ADSDSOObject" provider". Command parameters are unsupported with the current provider.
I don't have a linked server on my DB server to Active Directory so I'm just querying in the SSRS report design. Here is my query for my dataset. If I hard code an example it works. Just doesn't work when I pass a parameter. I've tried making it an expression (= sign), Tried several syntax's, Tried everything I can think of. Is this possible? or do I just need to push for a linked server?
Hi Everyone, Am a third year student doing work placement. Could anyone please give me clues on how to go about extracting data from a LDAP and then into an SQL database?
1 A defined subset of data is to be extracted from GDS on a nightly basis, 2 Then imported into a SQL database for quick & easy retrieval. 3 A web interface is required to present data retrieved from the SQL database.
I'm having a problem connecting with a Java application but I CAN connect using my .Net application - the user name and password are the same for both (using the same database on SQL Server Express 2005).
The error I get is: "com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "CORNERS" requested by the login. The login failed." An interesing note - I get the same message if the database is not running.
SQL Server Express 2005 is installed in mixed mode.
Here is my connection string in the .Net appplication: <add key="connectString" value="Server=(local);UID=sa;PWD=myPasswd;Database=CORNERS" />.
I have some SQL CE database .sdf file on my handheld, and I was trying to connect to that file via SQL Server 2005 Management Studio, and it does not work. I am wondering if there is any good tool that I can use on desktop to connect to sqlce .sdf database file on my handheld?
Hi, I have VS Pro 2005 and SQL Server Express 2005 installed on an XP Pro machine. When using VS 2005, in the 'Server Explorer' window I can see and attach to two SQL Server databases that I have set-up. But if I attempt to access the same databases from withn the 'Solution Explorer' window I get the following dialog box message,
" Required Component Missing (Dialog box heading)
Connections to SQL Server Files (*.mdf) require SQL Server Express to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkId=49251 "
I have uninstalled and then reinstalled both VS 2005 and SQL Server Express twice but it has helped.
I don't understand why I'm getting this error because all of the server configuration tools and connection utilities seem to work fine but VS say's that SQL Server Express is not installed.
Thanks,
CLN
P.S. I had another application that required MSDE 2000A to be installed. When I removed the other application I also removed MSDE 2000A and that's when the problem began.
I am having a problem with connecting to SQL Server 2005 database from Office Business Scorecard Manager 2005 (the connection string i am using is: provider=SQLOLEDB;Data source=server_name; Initial catalog=database_name). But the connection failed. Would please any experts here shed me any light on what is the problem and how to fix it then? As there is no any forum for Office Business Scrorecard manager 2005. Therefore I post my thread here as it is related to the connection to SQL Server 2005 database engine, thought here is the best place for this question.
Thank you very in advance for your kind advices and help. And I am looking forward to hearing from you soon.
I've used Sql Server 2000 and Visual Studio 2003 for a few years. I've started a new position and they have access to Sql Server 2005 Standard and Visual Basic 2005 Express which I'd like to use for a new project. So I installed Sql Server 2005 and then VB 2005 Express on my workstation. I didn't choose the Sql Server option for VB Express because I already had Sql Server 2005 Standard installed with a simple database created. I created a simple vb project that justs connects to the database but I get the following error.
Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0000, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. I looked at permissions in the database and it looks ok. I'm the db owner and I'm using Windows Auth. My connection string is
I thought I'd look at the starter kit to get some ideas about what the problem is, but when I started the movie starter kit project, it was upset that I didn't have Sql Server 2005 EXPRESS installed. Yea, but I do have Sql Server 2005 Standard installed. Any help will be greatly appreciated. Thanks.
I've installed, with default settings, VS 2005 and Server 2005 Express and cannot get VS2005 to connect. This is the error:
Unable to open the physical file "..path...mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process."
I shutdown Server 2005 but that didn't change anything. Any ideas?
I have installed MS Visual Web Developer 2005 Express Edition on my Notebook (WinXP Pro SP2). And it's working fine. Then I tried installed SQL Server 2005 Express Edition SP1. There is no error during installation and it seems to be working as I can see the SQL Server (MSSQLServer) service is running. As SQL server configulation manager., the protocal shared memory , named Pipes and TCP/IP are enabled.
But when I want to add connection using VWD 2005 by pointing to datasource (MS SQL Server database file and selecing file from .mdf file from SQL sample database file. Then test connection, I got a error that "An error has occurred while establishing a conneciton to the server. when connecting to SQL server 2005, this failure may be casued the the fact that under the default settings SQL server does not allow remote connections. (provider: SQL Interfaces, error: 26- Error locating server/instance specified)
Can you please advise what the missing part of my configulation ?
Hi there, I install microsoft server 2005 and I use the microsoft server management studio express tool to get connection and database tables set up. To connect to server: Connection server: localhostSQLEXPRESS Authentication: windows authentication Then I click on connect button
I create the tables in YCH1-1SQLEXPRESSDatabasesSystem DatabasesmasterTables where YCH1-1 is my machine name The table I have created is called staff I put in the code below in the web.config file but it does not work when I try to insert data into staff table <appSettings><add key="ConnectionString" value="Data Source=(local);Initial Catalog=Master; 
 Integrated Security=true;"/> </appSettings> Error in statement: connection.open() An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I have installed SQL Server 2005 Beta 2 on my PC. When I try to make aconnection to it from the DTS environment, I get following error:=============================================Could not get server list. (DTS Import/Export Wizard)=============================================[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or accessdenied. (Microsoft OLE DB Provider for SQL Server)----------------------------------------Making a connection from within SQL Server Management Studio works fine.If I try to connect from within a .NET program using SQLConnection, itfails on the conn.Open() call. But SQL Server Management Studio worksfine.Has anyone run into this? Could someone please help?Rob
