We're trying to figure out whether it's possible to link AD objects to SQL tables. For example, if we have a table of Users and a table of Companies, we'd like to enter the users into Active Directory, create the Companies as either OU's or Security Groups, then have those entities magically appear in the corresponding SQL Server tables. Is that possible? Thanks in advance.
View 1 RepliesHas 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 RelatedHello,
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
Any idea on how to query active directory to find out list of groups nad users etc..using sql server?
View 6 Replies View RelatedI look for and try to get data from Active Directory to MSSQL Server, but have same error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT name
FROM 'LDAP://office.experter.group'
WHERE objectCategory = 'Person' AND objectClass = 'user'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".
for the code:
select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://office.experter.group''
WHERE objectCategory = ''Person'' AND objectClass = ''user'''
)
I create linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI',
@srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject',
@datasrc=N'office.experter.group'
I've changed security setting, format of LDAP. Please advise me
It's me again guys. First let me say that I truly appreciate the knowledge that I see on this forum. Now on to my question.
How can I use SQL Server to import the active accounts from our Exchange Server? Thanks.
Everything I know I learned from the web.
Sometimes a user name will change in Active Directory and I have to change the name in SQL Server as well. So what I do is first delete the old user name from SQL Server's logins. However I wont be able to see the new user name in SQL Server to add it as a legit user until I roboot the server. Is there somekind of code I can execute to resynchronize with Active Directory again?
View 5 Replies View RelatedHere is what I tried to do:
exec sp_ActiveDirectory_SCP @Action = N'create'
error message output:
The service cannot be started, either becuase it is diabled or because it has no enabled devices associated with it.
Msg 22039, Level 16, State 1, Line 0
xpadsi90.exe failed.
How do I get around this problem?
I have SQL server 2005 + sp2.
Thanks,
Bobby
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??
I was wondering how exactly one can search the Active Directory with SQL Server 2000. There is all kind of stuff on the Internet about how to connect them and how useful it is to connect them together - plus how you can use Active Directory to query SQL Server, but I`ve never encountered any article where they explain how SQL Server can search the Active Directory.
I`m thinking about how one has their user-database in Active Directory with all users in groups, but only uses groups in SQL Server. How exactly can SQL Server figure out how a user belongs to a group?
Does Active Directory tell SQL Server about that when the user tries to access something from SQL Server, or does SQL Server already knows which group is accessing it as soon as the user authenticates in Active Directory?
Hi,
I am new to Microsoft Technologies, can any one say how to connect/ retrieve data's from Active Directory through SQL SERVER from scratch. I need with some sample codes.....
Thanks in advance
raj d.s
Hi,
Couple questions regarding SQL Server Active Directory Helper service:
a)What is its purpose?
b)Where can I get more detailed information about this service and
SQL Server 2005 services in general?
c)How can the following error during start up phase of this service
be avoided?
Error Message:
'0' is an invalid number of start up parameters. This service takes two start up parameters.
FYI: SQL Server 2005 is run on Windows XP professional.
Thanks,
Mike
Hello All,I have a linked ADSI Server to our company Active Directory andeverything is fine. I'm running queries and such using LDAP. BUT howcan I aquire a list of attributes for the classes and categories. Suchas if i wanted to see a user's telephone number and email address foruse in a corporate database? Is this possible?Thanks
View 4 Replies View Related
Does anyone know of any good references (books or web sites) that provide examples of querying AD from SQL Server? I have the database link setup and have done two very simple queries against AD but I would like to see more in-depth examples.
Thanks.
I have a SQL 2008 running on a server that is not part of an Active Directory Domain. I would like to add the server to Active Directory. How will this impact SQL in terms of the SQL users, Windows Authentication, and permissions?
View 2 Replies View RelatedHi all,
just want to confirm this:
We have a department and want to grant them access to various databases on a sql server 2000 instance. Can we create an AD group for these users and just add the group as a login to SQL Server? Or do we have to create a db user for each member of the group and map the logins one by one?
Thanks
Hello --I'm trying to get SQL Server 2000 on a Windows 2000 Server to be ableto query an Active Directory. We've got two domain servers one Win2000and one Win2003. However, I'm having problems:I've run the following query to setup the linked server:sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces','ADSDSOObject', 'adsdatasource'Using Enterprise Manager I've changed the login details for the ADSIlinked server to:Be made using the following security context:Username: MyDomainAdministratorPassword: ****I've then tried to run the following query in Query Analyzer:SELECT * FROMOpenQuery(ADSI,'<LDAP://CN=Users,DC=MyDomain,DC=com>;(&(objectCategory=Person)(objectClass=User));name,ad spath')But get the following error message:Could not execute query against OLE DB provider 'ADSDSOObject'.Does anyone have any ideas on what this message means or how to getthis working?Thanks--James Allanjames [at] allan-home.co.uk
View 2 Replies View RelatedWe have just installed a SQL Server 2000 (SP 3A) onto a computer thathas Windows-2003 Server on it. Now, we cannot get access to thatdatabase server from other computers. Seem like this may be an issuewith Active Directory.Our network consists of Windows-2000 Servers (SP 4) and Windows-2000workstations and Windows-XP workstations. We also have SQL Server 2000(SP2) in three Windows-2000 servers. All work fine. Recently, we get anew server that has already had Windows-2003 Server pre-installed.After we have installed SQL Server 2000 (SP 3A) on the new server, wefind that we cannot get access to that new database server from othercomputers.I have tried connecting to that new database server using QueryAnalyzer (using both the sa account and Windows Authentication), and Iget this error message:Unable to connect to server NEWDBSRV:Server: Msg 17, Level 16, State 1[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does notexist or access denied.I have tried connecting to that new database server using ISQL (usingboth the sa account and Windows Authentication), and I get this errormessage:DB-Library: Unable to connect: SQL Server is unavailable or does notexist.Unable to connect: SQL Server does not exist or network accessdenied.Net-Library error 53: ConnectionOpen (Connect()).I have also tried to register the new database server using EnterpriseManager (again, using both the sa account and Windows Authentication),and I get this error message:NEWDBSRV - SQL Server does not exist or access denied.ConnectionOpen(Connect())The only way that I can get access to the new database server is toopen Enterprise Manager directly on the new server.I have compared the properties of the new database server and those ofthe other existing database servers. The only differences are:-Other database servers are running Windows-2000. The new databaseserver is running Windows-2003.-The new database server is missing the "Active Directory" page-tagwhen I check its properties using Enterprise Manager. We use ActiveDirectory in our network, and the Active Directory is being run from aWindows-2000 server (that is currently also a database server).According to our network administrator, he has already put the newserver into our Active Directory. He doesn't know why it is missingthe Active Directory page-tag. He believes this has something to dowith its having Windows-2003 that is different from other servers(that have Windows-2000).Thanks in advance for any info.Jay Chan
View 2 Replies View RelatedI mapped a login created with an Active Directory Group on server A to a login on server B through a linked server on server A and received a null login error when attempting to connect.
I changed the Active Directory Group login to an individual active directory login and the connection worked fine.
I saw someone post online somewhere that Active Directory Groups don't work with linked server by design--but I wanted to get confirmation on this. Can anyone confirm this, particularly someone from Microsoft?
How can I copy data like firstname, lastname, email from Active Directory into a SQL Server table?
View 4 Replies View RelatedI need to add one of our servers to active directory. I found some documents on SQL 2000 but no good document on SQL 2005. Can you please guide me to the documentation for this? Thanks
Hi,
I had to remove AD from win server 2003 after that I didn't have any SQL to uninstall in Add or remove programs folder.
Is there any other way to uninstall SQL Server?
best rgds - timppa
Hello everyone,
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:
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active
Directory Services 2.5', @provider=N'ADsDSOObject', @datasrc=N'adsdatasource'
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.
Any thoughts/suggestions are greatly appreciated!
Hi all! I know I might sound stupid, but I have this problem. I have developed my database on my laptop which does not use Active directory. Lets say I access the database with: SPIDERMANDB1 on SQL server, where the name of my laptop is SPIDERMAN, and the database is DB1. Now, I create an SQL script from sql server and make the necessary changes, like changing the computer name to say, SUPERMAN where my deployment server's name is SUPERMAN. But when I run the SQL script on the SUPERMAN server, it gives me an error like: "User or role SUPERMANDB1 does not exist in this database". I have deployed databases in this way on servers without Acive directory, so I'm sure its not a mistake on my side. So how am I supposed to go around this active directory thingi! Please help me out! I'm counting on u guys!
Wes
hi,
I am trying to run a query analyzer via query against ad. Below is the error I am getting. How to extract the directReports attribute values from windows 2003 active directory
SELECT * FROM OPENQUERY( ADSI,
'SELECT directReports FROM ''LDAP://192.168.1.24/dc=xxx,dc=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by name')
Err: Server: Msg 7346, Level 16, State 2, Line 1
Could not get the data of the row from the OLE DB provider 'ADsDSOObject'. Could not convert the data value due
to reasons other than sign mismatch or overflow.
plz do the need full
thanks & regards
venkey
My question is I have a SQL Server running on Web Server which is a member of a 2000 Active Directory, I only grant access to the database via Global Groups from the Active Directory. When I log onto the database via Windows Authentication the actual user shows up in the master.dbo.sysprocesses table, I can tell what database that process is going to but not how that user is being translated to the Global Group that was actually given access. I need the actual database user name which is the Global Group name that had permissions granted via user defined database roles so that I can do some pre-processing in an ASP.NET application so that I know what parts of a form are updatable or not.
View 1 Replies View RelatedHi,
I'm trying to extract all the users and their membership to groups, and the membership of groups to groups from active directory though a link to server.
I can get the users. I can get the groups.... individually.
I can't get the info of what user is a member of or who are members of a group.
Anyone know how to do this or am I going to have to right a vb app? (Anyone already got the code...)
I want to load this data into tables for reporting in my Data Warehouse.
Cheers
Chris
Is there anyway,can we find the list of servers by querying at active directory?
View 3 Replies View RelatedI am trying to setup querying Active directory from sql for the first time.
We are running on windows server 2012 and using sql 11.0.2100.60. Have tried the following
sql is on sever dev
AD is on sever DO
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
GO
[Code] ....
I get the following error when I try and query
Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "SELECT name
FROM 'LDAP:// xxxx.internal'
WHERE objectCategory='Person' AND
objectClass = 'contact'" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
I have more than 3000 Active Directory Users, I have created Role Level Security on one table by using Suser_name(), Now all the active directory users need to connect sql server and access the database role object. How can I achieve that without Using .net?
I am able to add all active directory users to sql server in one go, but I am also trying to achieve same time to map with database role as well? Is it possible ?
I am running into a weird issue with a new SQL Reporting Services 2014 server I built. I installed SQL Reporting 2014 on Windows Server 2012 R2 and configured Kerberos, but the site is extremely slow. After some reconfiguration and log captures I have determined the issue has to do with the Kerberos setup, however I am running a similar configuration with SQL Reporting Services 2008 on Windows Server 2008 R2 and do not run into the same errors.
The error I see while using Wireshark is KRB Error: KRB5KDC_ERR_BADOPTION NT Status: STATUS_NO_MATCH. When I drill down the into the error I can see the kerberos string is testprjmnmtreports14.company.com, which is the URL we are using to access the site. I made sure to add that name as an SPN for the service account that is running SQL Reporting Services, however I still receive the error.
Then I tried configuring the site to run without a hostheader, so I accessed the site with the server name, ECTSTSQLRS5, and the site works perfectly fine, no errors are reported either. So it seems I have isolated the issue down to Kerberos but I am not sure how to resolve it. Here is some more information about my environment:
DNS/URL used: testprjmnmtreports14.company.com
Server Name (FQDN): ECTSTSQLRS5.company.int
AD Domain Name: company.int
Server Version: Windows Server 2012 R2
AD Functional Level: 2008 R2
As you can see I am trying to use a .com address but my AD domain is .int which I think is the issue, but I do not have the same problem on my other server that is running Windows Server 2008 R2. What do I need to do to allow my new site on 2012 R2 to work with this DNS Alias?
Hi all,
From SQL Server 2000 Import wizard if we use Copy Objects & data between SQL Server Databases it'll copies tables with primary key. Similar to this what is the alternative method in SQL Server 2005.
Please suggest me to solve the problem
Thanks in advance
Karna
Hi all,
Now I want get AD value(e.g file path),how can I get this value from AD?
Thanks