IC856696.png' alt='Examples Of Active Directory Queries Sql' title='Examples Of Active Directory Queries Sql' />Querying Active Directory Through SQL Server.Integrating your application with Active Directory or another LDAP based directory is a common requirement in many business applications.Almost every language has a way to query LDAP but little known is the approach of integrating SQL Server with your LDAP controller.This quick article will guide you through setting up and using LDAP queries through SQL Server 2.Active Directory as the LDAP controller.First, you need to create a SQL Server linked server.Set it up through SQL Server Enterprise Manager Open SQL Server Enterprise Manager.Go to the database server to which you will be adding the linked server.Expend SecurityRight click on Linked Servers and click on New Linked Server.Fill in the following.Under the General tab.Linked Server adsi or whatever you want to call itServer type select Other Data Source.IC856698.png' alt='Examples Of Active Directory Queries Sql' title='Examples Of Active Directory Queries Sql' />Provider name select OLE DB Provider for Microsoft Directory Services.Under Provider Options check that Allow In.Process is checked.Leave the rest of the fields blank.Under the Security tab.Local Login sql. In this case all features are fetched from the layer named polylayer, and all attributes of those features are returned.This is essentially equivalent to accessing.IC197332.gif' alt='Examples Of Active Directory Queries Sql' title='Examples Of Active Directory Queries Sql' />Server.User. Remote User ntaccountdomain.Remote Password user.Password for the above domain account.Through Query Analyzer Change the Allow.In. Process registry key under HKEYLOCALMACHINESOFTWAREMicrosoftMSSQLServerProvidersADSDSOObject to dword 0.Edit the registry manually or put the following in a.Windows Registry Editor Version 5.HKEYLOCALMACHINESOFTWAREMicrosoftMSSQLServerProvidersADSDSOObject.Allow. In. Processdword 0.Open Query Analyzer or your choice of query toolConnect to the server to which you will be adding the linked server.Change the provided values and execute the following code Change adsi to the desired name of the linked server.ADSDSOObject. Change adsi to the desired name of the linked server.Change sql. User to the username of local sql server user. Driver San Francisco 2011 Pc Iso Torrents here. Change domain. Nameuser.Name to a domain account.Nameuser. Name or user.Namedomain. Name.Change domain. User.Password to the password of the domain account.Server. User. domain.Nameuser. Name. User.Password. Or you can just fill in the values on top of the following script and run that declare linked.Server. Sql nvarchar4.Server. Name varchar1.Sql. Username varchar1.Username varchar1.User. Password varchar1.Set the local sql server user.Sql. Username sql.User. format can be domain.Nameuser. Name or user.Namedomain. Name.Username domain. Nameuser.Name. User. Password domain.User. Password. set linked.Server. Name adsi.Server. Sql. exec spaddlinkedserver.Server. Name. ADSDSOObject.Server. Name. Sql.Username. Username.User. Password. Server.Sql. Run a query to verify that the linked server works.The query below will give you all the users in the dcica,dccom change that to match your own domain select.Name. s. AMAccount.Name. telephone. Number.Delivery. Office.Name. from LDAP dcica,dccom.Category Person. Class user.Below, is an alternate syntax which you can use to apply ldap filters with almost universal syntax.The following will get all the users in LDAP but limit the result set to those users whos given sn names are not empty.It will also apply a filter to the division attribute and exclude any records that match system and generic.Filter nvarchar1.SQL nvarchar4. 00.Set the filter to exlude objects that have a division of System and Generic.Filter divisionSystem Generic.Create an ldap query to get all users under dcica,dccom.SQL. select given.Name as first. Name.Name. display. Name.AMAccount. Name as account.Name. telephone. Number as phone.Number. mobile as cell.Phone. Number. mail as email.Address. department.Delivery. Office.Name as site. Name.LDAP dcica,dccom.CategoryPersonobject.Classuser ldap. Filter.Name. s. AMAccount.Name. telephone. Number.Delivery. Office.Name. where given.Name is not null.SQL. Some things to note LDAP is case sensitive, if you try using ldap, the query will throw an error.Querying the Active Directory server will work fine from Query Analyzer even if you do not run spaddlinkedserverlogin.However, if you try to execute the query from the web with something like Cold.Fusion, you will get an error similar to MacromediaSQLServer JDBC DriverSQLServerOLE DB provider ADSDSOObject reported an error.The provider indicates that the user did not have the permission to perform the operation.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |