Trying To Query 'Active Directory' In An SSIS Package.

May 20, 2008


Hi All

I am trying to run a query against our 'Active Directory' and I keep getting errors. I have done the following:

1. Set up Linked Server (using the OLE DB For Directory Services) and also set the login information:


EXEC sp_addlinkedsrvlogin 'ADSI', False, 'SQL_Usr','DDEMOJillSmith', 'PassWord'

2. Created a connection in SSIS
3. In My SSIS Package I have created a dataflow select the AD connection 'ADSI'.
4. Tried to run the following query:

SELECT samaccountname, adspath, sapUsername, mail, info, costCenter, pager, mobile, facsimileTelephoneNumber, telephoneNumber, company,
department, title, sn, givenName, name, employeeNumber FROM
''LDAP://OU=Main_Users, DC=DemoAD,DC=com'' WHERE objectCategory = ''Person'' AND objectClass= ''user''

And it fails - I can get this working in SQL 2000 DTS by using an Open Query statement!

The Error message I get when trying to test the query is:

Error at Export From AD [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00000000.

Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)


I have event tried the working OPEN QUERY in SSMS and I get the following:


Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "SELECT samaccountname, adspath, sapUsername, mail, info, costCenter, pager, mobile, facsimileTelephoneNumber, telephoneNumber, company,

department, title, sn, givenName, name, employeeNumber FROM

'LDAP://OU=DEMO_Users, DC=DEMO,DC=com' WHERE objectCategory = 'Person' AND objectClass= 'user'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

Am I missing something?

Thanks Gopher

View 4 Replies


ADVERTISEMENT

Active Directory Connection From SSIS

May 12, 2006

I'm trying to query against AD to grab some data. I've done this setup and got it to work at my location but can't get it working at one of my customers.
Per information I've found via this site I did the following:
Task: from SS2005, select data elements from Active Directory and populate in ODS (using an
SS2005 Package in SSIS)


I created a linked server on the MS2005

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
I then created the following View (in the Master DB):
CREATE VIEW viewADContacts
AS
SELECT [NAME],MAIL
FROM OPENQUERY( ADSI,
'SELECT NAME, MAIL
FROM ''LDAP://#######/ DC=####,DC=###''
')
The View created w/ no errors

When I execute
select * from viewADContacts
I get this error:
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".

Any thoughts on this error? Again, I did the exact same thing at my office (against my local AD) and it worked fine.

Thanks in advance

Tom

View 3 Replies View Related

SSIS Transfer From Active Directory

May 22, 2008

I hope this topic is appropriate for this forum. If not, I pre-apologize.


I have an SSIS package that takes data from active directory and dumps it to a table.

Here is the query I am using:

SELECT displayName, Mail, Title, physicalDeliveryOfficeName, description, telephoneNumber, department
From 'LDAP://ourserver'
WHERE objectClass='user' AND objectCategory='Person'

I am dumping it into a table on SQL Server where the columns are all of data type: NTEXT.


All columns come across GREAT except the column DESCRIPTION. We keep a piece of information in that column that is important and without it the SSIS is worthless. What is coming across in that column is the following:

System.Object[]

No values are coming across. It should be character data up to 10 in length.

Anyone see this or know of something that I can do to force the "real" data to come through?

Thank you in advance for any consideration you give.

James

jamestow@yahoo.com

View 16 Replies View Related

Transact SQL :: Active Directory Data Into Server Using SSIS Or SP?

May 29, 2015

How can I copy data like firstname, lastname, email from Active Directory into a SQL Server table?

View 4 Replies View Related

Query Active Directory

Aug 16, 2007

[code]
SELECT * FROM OPENQUERY(ADSI, 'SELECT name FROM 'LDAP://mydomain' WHERE objectClass='User'')

[/code
it worked just fine. but i want to select everything from active directory base on NT account (the account that user uses for their window logs in) and the user has to belong to a certain group (for instance: group = student)

now how do i do that? can you guys help. thank you

View 14 Replies View Related

Using Linked Server To Query Active Directory From SQL??

May 28, 2004

I want to create a view in SQL populated with users from our Active Directory. I have learnt that this can be done using linked server. I have tried using the following:

sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
go

sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'false', @locallogin = 'sa', @rmtuser = 'lok_applications', @rmtpassword = '9dfFfG374GoiAo6yxxc8oZ'

SELECT *
FROM OpenQuery( ADSI,
'SELECT * FROM "LDAP://194.22.1.18/DC=lok,DC=net"')

I keep getting this error no matter what I try:
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].

Any ideas why??

View 5 Replies View Related

Query LDAP/Active Directory In Stored Procedure

Apr 28, 2004

I know you can access LDAP via ADO.NET and the ADsDSOObject Provider.

Is there away to do it within a SQL Server Stored Procedure?

Thanks,
Tim

View 2 Replies View Related

Active Directory Query 1000 Page Size Limitaion

Jul 23, 2005

Hi.We need to create a view of our active directory users (we have 2500).I found out that there is max page size of 1000, so we cannot get moredata.Anyone found a solution to that problem?Thanks

View 1 Replies View Related

Using Provider For Microsoft Directory Services For Querying Active Directory

Apr 6, 2007

Has anyone used this successfully from an OLEDB source component, or even from the Execute SQL Task? I've seen some examples of using a script component, but nothing that uses it through a connection manager.

View 6 Replies View Related

Root Directory Of SSIS Package

Jan 12, 2008

Hi everyone!
I have a configuration file, I want to place it in somewhere of SSIS package to extract configuration information. But i dont know where is root directory. can you help me?
Thanks you very much

View 3 Replies View Related

Reliably Detecting The Current Directory For An Executing SSIS Package?

Oct 30, 2007

I'm attempting to find a reliable way to determine the file system folder in which the current package is located. (Please note that for this purpose I have no need to support SQL Server deployment, so the fact that there is no current folder in that context is not in scope for this post. )

Anyway, I've added the following code to a Script task in one of my packages:




Code BlockMsgBox(Environment.CurrentDirectory)




When I run the package from DTEXEC I get this the current working folder from which I executed the DTEXEC command line. When I run the package from DTEXECUI I get "C:Projects2005TFSRootSSISCurrentBranchProject", which is the actual folder where the package is located. When I run the package from within Visual Studio (right click, execute package) I get "C:Projects2005TFSRootDocumentationSSIS" which is what is throwing me off. I can't tell where this path is coming from; it is a valid path on my machine, but why it's showing up as the current folder is the mystery. There's no code there and never has been, although I do check in and check out documentation stored in this folder.


I've looked around online but have had no luck in finding any information aboutreliably determining the location of an executing SSIS package. The best I have found is this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2202954&SiteID=1) which doesn't address the issue I'm seeing.

In short, I'm looking for a consistently reliable mechanism that will allow an SSIS package to be aware of its location at run time, regardless of the mechanism (DTEXEC, DTEXECUI, BIDS, etc.) through which the package is executed. My hopes aren't all that high, but I figured if I was going to get an answer anywhere it would be here.

Has anyone else had success with this?


Thanks in advance!

View 3 Replies View Related

How Can I Get AD(active Directory) Value In T-SQL?

Nov 6, 2001

Hi all,
Now I want get AD value(e.g file path),how can I get this value from AD?

Thanks

View 1 Replies View Related

Active Directory

Nov 24, 2004

Hie,
Someone can tell me haw can i do in order to migrate my server sql to active directory.
What is the step

View 1 Replies View Related

SQL Server And Active Directory

Jun 6, 2004

Hello,

I have recently upgraded my the server that runs SQL Server to an Active Directory Domain Controler. Now I can't connect to the SQL Server from ASP.NET Applications when the application is not located on the local machine. The error message I get is SQL Server does not exist or access is denied.
I have no problems connecting with QueryAnalyer and Enterprise Manager from my workstation. I have added the Sql Server to the directory via the "Active Directory"-tab in the Property window for my Sql Server Registration i Enterprise Manager.
If I copy a directory from the wwwroot on my workstation to the server the application has no problem to connect so the connectionstring seams to work fine.

Any ideas?

Regards,
Kalle

View 1 Replies View Related

Working With Active Directory

Nov 27, 2003

hi,

we have recently completed an upgrade to 2000 server and now have AD on our network.

How do i go about querying this from any of my SQL 2000 servers?

I have found a few websites that mention adding a linked server. I have never done this and am not sure how to query a linked server, if that is the way to go.

can anyone offer some advice please?:confused:

TIA

View 1 Replies View Related

Migrating To AD (Active Directory)

Sep 20, 2004

A little background, We have a DEV Server running SQL Server 2000. This is the first of many to be migrated from out NT Domain to our new AD (active directory Domain). All Domain user accounts have already been migrated.

When they migrated this first Server running SQL Server, I am getting the following error when I try to make the owner of a job (any job) run by the SQL Server Agent a domain account in the new AD - when I switch the ownership back to our old NT Domain, it works fine.

I am getting this error:
The job failed. Unable to determine if the owner (domainusername) of job testjob has server access (reason: Could not obtain information about Windows NT group/user 'domainusername'. [SQLSTATE 42000] (Error 8198)).

note that this is happening to all windows authenticated sql server accounts on this Server. All of these account are in the local Admin group on the Server.

Does anyone know what needs to be done in SQL Server to make the AD migration seemless???? I need to try and find this out before we begin migrating Production Servers. Thank you!!

View 6 Replies View Related

Do I Need Active Directory For Sql2000?

Jul 12, 2001

Hi folks,

I'm try'n to find out if i need active directory for sql2000 if my primary network is running on windows2000? What are the pros and cons? Thanks!



Joe R.

View 1 Replies View Related

Active Directory Update

Jul 22, 2004

Hi!
I want to write a trigger that add a new computer account in my active directory when I do an Insert in my MSSQL table.

I know how to use SELECTstatements using LDAP but I want to do a INSERT statement. Is that even possible?
Can you write vb code directly in SP i mssql 2000?

What I think I have to do is to have a vbscript that does the adding then call the script using exetended SP cmd execute passing the name to the script.

If someone has a another solutions please let me know!

Regards..

View 1 Replies View Related

Sync Active Directory

Nov 3, 2005

Hi there,

Is it possible to, somehow, get a specific users password from active directory? The reason I ask is that I am writing a new system and really don't want the users having to remember yet another password, but rather be able to use there network password? I would like to write the logon section myself and not use any built in functions that anything may have.

Please can someone advise. I don't think it is possible but have been asked to persue the issue.

Thanks

View 2 Replies View Related

Migrate To Active Directory

Sep 20, 2006

Hi,
I want to migrate my sqlserver to active directory.
Someone can tell me what is the procedure and how can i do ?

Someone have already do this migration ?

Regards

View 5 Replies View Related

Active Directory Implementation

Jul 20, 2005

We are implementing Active Directory. I need to know if this will presentany issues/changes for our SQL Server 2000 servers.TIADave Edwards

View 1 Replies View Related

Web Assistant And Active Directory

Jul 20, 2005

HiI've created a stored procedure (see below) which accesses the ActiveDirectory and SQL server to get "real names" back. When I run thestored procedure in Query Analyzer it returns the expected results,however when I try to create a Web Assistant job based upon theprocedure I get the SQL-DMO message:Error 7410 Remote Access not allowed for Windows NT Useractivated bySETUSER.The procedure is being run (and the job created) as the account whichowns the SQL Server installation, and this account has AD adminpermissions.Any suggestions?CREATE VIEW dbo.vw_account_adASSELECT a.Name AS ad_name, dbo.Accounts.*FROM dbo.Accounts INNER JOINOPENQUERY(ADSI,'select SamAccountName, Name FROM''LDAP://w2k-bspad1/ ou=users,ou=bsp,DC=ad,DC=bl,DC=uk'' whereobjectcategory=''person'' ') a ONSUBSTRING(dbo.Accounts.Account_Name,CHARINDEX('', dbo.Accounts.Account_Name) + 1,LEN(dbo.Accounts.Account_Name) - CHARINDEX('',dbo.Accounts.Account_Name)) = a.SamAccountNameCREATE PROCEDURE [dbo].[usp_event_report] ASSET ANSI_NULLS ONSET ANSI_WARNINGS ONSELECT Code_Name, Account_Name + ' ('+ad_Name+')' as 'Account Name',Date_Occured, ResultFROM Usage_Codes, Usage, vw_account_adWHERE Usage.Code_ID = Usage_Codes.Code_IDAND Usage.Account_ID = vw_account_ad.Account_IDAND datepart(month,Date_Occured) = datepart(month,getdate())ORDER BY Code_Name, Account_Name, Date_OccuredGOChloe CrowderThe British Library

View 2 Replies View Related

Active Directory Authentication

Oct 24, 2007

Hi there.

I have a request to build some reports that are specific to each user. Only the autheticated user should be able to see their report data and no one elses etc. How do I get data for the current autheticated user (via AD)? If this is via parameter, how do I hide the username/password in the url?

I am sure this has been done, but I couldn't find any good examples.

Thanks, Mike

View 2 Replies View Related

Querying The Active Directory??

Nov 19, 2007



Can someone please tell me or provide a link explaining how I can query the active directory for
usernames from sql server 2005. I'm actually creating usernames on the fly and I need to check if they already exist in the active directory. Thanks.

View 3 Replies View Related

USING ORACLE WITH ACTIVE DIRECTORY

Apr 2, 2008

We want to use Active Directory with Oracle for User Authentication and accessing Oracle as well as storing the details in Oracle. Active Directory stores Information regarding Users, Groups & Policies etc.

We want to provide the access in Oracle for the users available in AD as well as export Complete User Information from active directory and keeping the updated information into some Oracle tables. What is the optimal method?

What configuration needed at Server/ Client End and How to do the same?
Kindly provide the steps. Please do the needful. Thanks.

View 4 Replies View Related

Active Directory And SQL Authentication

Feb 6, 2008

We currently have Active Directory within our domain all Server 2003 based. We also have a SQL2005 database stand alone server (not currently joined to the domain). What we would like to do is utilize single sign on. Currently our users have to log into windows, then open an "in-house" program which asks for a different set of credentials for the SQL2005 database. How do we intergrate Active Directory login to also authenticate to the SQL database? Can we just join the stand alone SQL server to the domain, then from there add the Active Directory "security groups" into the database? Could someone point me in the right direction, thanks!

View 1 Replies View Related

Active Directory And SSRS

Jan 24, 2007

Is it possible to create a report in SSRS that queries Active Directory data such as user's phone extension, email address etc

What would be a good way to do this?

Thanks,

Nisha

View 7 Replies View Related

Active Directory Reports

Mar 29, 2007

I'm not sure this is an actual reporting services question but has someone else created reports for active directory in Reporting Services

I want to create a report with users and their respective manager. I have this working in reporting services but I just want the manager name how could I strip out all the other information in the manager field??



SELECT personalTitle, manager, name, employeeid, distinguishedName
FROM 'LDAP://dc=xxx,dc=xxx,dc=xxxt'
WHERE objectClass = 'user' AND objectCategory = 'Person'
ORDER BY name



I get this as the manager name, I just want his name



CN=Smith, Kurt,OU=Financial,OU=DataControl,OU=Users-Groups,DC=xxx,DC=xxx,DC=xxx



Thank you,

View 6 Replies View Related

Connect SQL With Active Directory

Jun 1, 2006

Hi everybody. I have this problem: I have to access to the Active Directory of a remote server from my computer using SQL and make searches based on the LDAP of the server. Someone told me to use Add Linked Server, but I don't know how to do it with the Active Directory. Thanks for the help.

View 3 Replies View Related

Querying Active Directory

Jan 8, 2008

Need help understanding error message from CLR:

I encapsulated calls to Active Directory in a dll. This dll works very well for a call or a few calls. However when I wrote a winapp that calls many times repeatedly to this dll and consequently to DirectorySearcher, I recieve the following message after many iterations have been made: (while debugging, or in release mode same problem occurs)

Managed Debugging Assistant 'ContextSwitchDeadlock' has detected a problem in 'D:DevProjectsADQuerySysRegLoadPersGrpsSRLoadPersGrpsSRLoadPersGrpsinReleaseSRLoadPersGrps.vshost.exe'.
Additional Information: The CLR has been unable to transition from COM context 0x1a0998 to COM context 0x1a0b08 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.


Continue & breakAll after the above error thrown caused this system error code to be generated: H80131c25


Here's one of the subs in the dll that is causing the problem. After multiple calls to the directorySearcher, one or another call fails with the above error. (on the FindOne method here, but on the .GetDirectoryEntry method in another sub, random which one fails, it's the number of iterations that is the problem) Question I have is: Is the only way to stop this error to reduce or slow down the calls to DirectorySearcher?


Private Sub GetGroupName(ByVal GroupName As String, ByRef strDistinguishedName As String, ByRef strMsg As String)
Dim strFilter As String
Dim entry As DirectoryEntry
Try
strFilter = "(&(objectClass=group)(cn=" + GroupName + "))"
Dim myArr() As String = {"distinguishedName"}
Dim mysearchroot As DirectoryEntry = New DirectoryEntry(myLDAPPath)
Dim myDirectorySearcher As New DirectorySearcher(mysearchroot, strFilter, myArr, SearchScope.Subtree)
Dim mySearchResult As SearchResult = myDirectorySearcher.FindOne
If mySearchResult Is Nothing Then
Throw New Exception("Get Group Name: No groups by name: " & GroupName)
End If
entry = mySearchResult.GetDirectoryEntry()
strDistinguishedName = entry.Properties.Item("distinguishedName").Value.ToString
'cleanup
entry = Nothing
mySearchResult = Nothing
myDirectorySearcher.Dispose()
mySearchResult = Nothing
Catch e As Exception
strMsg = "Failed to Get Distinguished Group Name for: " + GroupName + " " + e.Message
strDistinguishedName = ""
End Try

End Sub




View 4 Replies View Related

Update Active Directory

Jan 9, 2008

Is there a way to update active directory through SSIS?

We have an HR application that holds our employee information and I need to pull the data out of that applications DB to update AD.

View 3 Replies View Related

Integrating RS With Active Directory

Apr 5, 2007

Just like out of the box windows authentication and authorization is supported, has anyone integrated reporting services with Active Directory or at least point me to the correct resource?



Thanks.

View 1 Replies View Related

Authenticate Against Active Directory

Jun 5, 2006

We are writing an enterprise application in which the user logs in by entering their username and password in a form. The user will enter their actual Active Directory credentials. I want to pass the username and password to a stored procedure that will check for a valid user account in an Active Directory. In Sql Server 2005 I know I could write a small .NET library that uses System.DirectoryServices , register it in SQL and access it from an stored procedure.

Can it be done in Sql server 2000?

The reason we are not just autheticating in the .NET code of the application is that we found that it does not work on Windows 98 cleints, hence the need to go through sql server.

Thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved