Cannot reference new attribute in Active Directory

We have had an existing linked server that we used to query AD and everything was working as expected. We had to add a new attribute so the AD schema was extended.

Now when I try to reference the new field I get the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'ADsDSOObject' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'ADsDSOObject' IColumnsInfo::GetColumnsInfo returned 0x80004005: The provider did not give any information about the error.].

Here is the code that gives the error:

SELECT employeeNumber as Employee_Number,
NECASupervisor
FROM OpenQuery(ADSI,
'SELECT employeeNumber,NECASupervisor
from ''LDAP://OU=NECA,OU=UserAccounts,DC=neca,DC=pri''
')

This query works fine:

SELECT employeeNumber as Employee_Number
FROM OpenQuery(ADSI,
'SELECT employeeNumber
from ''LDAP://OU=NECA,OU=UserAccounts,DC=neca,DC=pri''
')

Note: The field NECASupervisor is the new AD attribute. It is in the UserAccounts OU. If I run the same type of query using VB script it finds the attribute.

Anyone have any ideas on this?

Thanks

It turns out that once the Sql Server was rebooted, the query was able to reference the new AD attributes.

Cannot reference new attribute in Active Directory

We have had an existing linked server that we used to query AD and everything was working as expected. We had to add a new attribute so the AD schema was extended.

Now when I try to reference the new field I get the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'ADsDSOObject' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'ADsDSOObject' IColumnsInfo::GetColumnsInfo returned 0x80004005: The provider did not give any information about the error.].

Here is the code that gives the error:

SELECT employeeNumber as Employee_Number,
NECASupervisor
FROM OpenQuery(ADSI,
'SELECT employeeNumber,NECASupervisor
from ''LDAP://OU=NECA,OU=UserAccounts,DC=neca,DC=pri''
')

This query works fine:

SELECT employeeNumber as Employee_Number
FROM OpenQuery(ADSI,
'SELECT employeeNumber
from ''LDAP://OU=NECA,OU=UserAccounts,DC=neca,DC=pri''
')

Note: The field NECASupervisor is the new AD attribute. It is in the UserAccounts OU. If I run the same type of query using VB script it finds the attribute.

Anyone have any ideas on this?

Thanks

It turns out that once the Sql Server was rebooted, the query was able to reference the new AD attributes.

cannot query directory services

Dear all,
I would like to query informations that are located in the (AD) Active
Directory Windows 2003 with my server Windows 2000SP4 and MS SQL 2000 SP4 in
the same domain and AD.
I create the link server with the following script :
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
GO
and I query AD with the following :
SELECT [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI,
'SELECT Name, SN, ST
FROM ''LDAP://ADSISrv/ OU=myOU,DC=mycompagny,DC=com''
WHERE objectCategory = ''Person'' AND
objectClass = ''contact''')
GO
Then I get the following error :
Server: Msg 7321, Level 16, State 2, Line 1
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].
I clarify that I used Integrated Windows security and also used the USERID
and password of service SQLserver as recommanded to launch my query.
But I get the above error. Why ?
Thanks for your next reply,
JobHi
You may get a better response in the ADSI news groups e.g
microsoft.public.adsi.general, but I think the error is probably because the
syntax of your query is incorrect, check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/adsi_ldap_provider.asp
John
"Job" wrote:
> Dear all,
> I would like to query informations that are located in the (AD) Active
> Directory Windows 2003 with my server Windows 2000SP4 and MS SQL 2000 SP4 in
> the same domain and AD.
> I create the link server with the following script :
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject', 'adsdatasource'
> GO
> and I query AD with the following :
> SELECT [Name], SN [Last Name], ST State
> FROM OPENQUERY( ADSI,
> 'SELECT Name, SN, ST
> FROM ''LDAP://ADSISrv/ OU=myOU,DC=mycompagny,DC=com''
> WHERE objectCategory = ''Person'' AND
> objectClass = ''contact''')
> GO
> Then I get the following error :
> Server: Msg 7321, Level 16, State 2, Line 1
> 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].
> I clarify that I used Integrated Windows security and also used the USERID
> and password of service SQLserver as recommanded to launch my query.
> But I get the above error. Why ?
> Thanks for your next reply,
> Job
>
>sql

Cannot get AD groups to work in SQL 2k5

I'm trying to get Active Directory groups to work in SQL Server 2k5 so my
users can log in with windows authentication. So I've set up the group in
AD, added the domain users to it, added the group to MS SQL security users,
and assigned a default database to the group. For some odd reason my users
are not able to log in though; I can quickly check this by trying to set up
a
ODBC connection System DSN.
The odd part about this is that if I add a domain user to MS SQL, they have
no problem logging into the database. Understandably I want to avoid adding
each and every user to my database, therefore the use of AD groups that is i
f
they would work.I would expect users to be able to connect via group membership. Do you see
the group members listed when you execute xp_logininfo? Does the group show
as the permission path for a specific user?
EXEC xp_logininfo
@.acctname = 'MyDomain\MyGroup',
@.option = 'members'
EXEC xp_logininfo
@.acctname = 'MyDomain\MyGroupMember'
Hope this helps.
Dan Guzman
SQL Server MVP
"Chandler Chao" <ChandlerChao@.discussions.microsoft.com> wrote in message
news:A2720F69-543C-4DF4-8079-56ACA8776D51@.microsoft.com...
> I'm trying to get Active Directory groups to work in SQL Server 2k5 so my
> users can log in with windows authentication. So I've set up the group in
> AD, added the domain users to it, added the group to MS SQL security
> users,
> and assigned a default database to the group. For some odd reason my
> users
> are not able to log in though; I can quickly check this by trying to set
> up a
> ODBC connection System DSN.
> The odd part about this is that if I add a domain user to MS SQL, they
> have
> no problem logging into the database. Understandably I want to avoid
> adding
> each and every user to my database, therefore the use of AD groups that is
> if
> they would work.|||I was just able to get this working with a some help from this NG.
My SQL Server is configured for Mixed authentication.
I set up my AD groups and added users.
I added logins for each AD group in SQL Server.
I added database users for each AD group and assigned permissions to
objects.
Hope this helps..
Michael
"Chandler Chao" <ChandlerChao@.discussions.microsoft.com> wrote in message
news:A2720F69-543C-4DF4-8079-56ACA8776D51@.microsoft.com...
> I'm trying to get Active Directory groups to work in SQL Server 2k5 so my
> users can log in with windows authentication. So I've set up the group in
> AD, added the domain users to it, added the group to MS SQL security
> users,
> and assigned a default database to the group. For some odd reason my
> users
> are not able to log in though; I can quickly check this by trying to set
> up a
> ODBC connection System DSN.
> The odd part about this is that if I add a domain user to MS SQL, they
> have
> no problem logging into the database. Understandably I want to avoid
> adding
> each and every user to my database, therefore the use of AD groups that is
> if
> they would work.

Cannot Generate SSPI Context.

we installed SQL Server 2000 and a new test database, 2 groups were created:
BobAdmin
BobUser
in Active Directory Users & Computers
and users added to each group and everything was fine,we are now getting an
error "Cannot Generate SSPI Context. " when we try and open the database app
on the workstations.
The only change is we have changed the Main Administrator Password, would
this affect the server or the log-on's to SQL Server and has anyone fixed
this problem that could help.
Many Thanks
Jimleake@.hotmail.com
Does SQL Server know about these groups?
http://www.aspfaq.com/
(Reverse address to reply.)
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>
|||I had a similar problem and it turned out to be a DNS issue.
Have you reviewed this article?
http://support.microsoft.com/default...b;en-us;811889
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>
|||Large time differences between systems can also cause SSPI context failures.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>
|||Also check the step-by-step guide how to troubleshoot this error a
http://support.microsoft.com/default...889#appliesto.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>

Cannot Generate SSPI Context.

we installed SQL Server 2000 and a new test database, 2 groups were created:
BobAdmin
BobUser
in Active Directory Users & Computers
and users added to each group and everything was fine,we are now getting an
error "Cannot Generate SSPI Context. " when we try and open the database app
on the workstations.
The only change is we have changed the Main Administrator Password, would
this affect the server or the log-on's to SQL Server and has anyone fixed
this problem that could help.
Many Thanks
Jimleake@.hotmail.comDoes SQL Server know about these groups?
http://www.aspfaq.com/
(Reverse address to reply.)
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>|||I had a similar problem and it turned out to be a DNS issue.
Have you reviewed this article?
http://support.microsoft.com/defaul...kb;en-us;811889
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>|||Large time differences between systems can also cause SSPI context failures.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>|||Also check the step-by-step guide how to troubleshoot this error a
http://support.microsoft.com/defaul...1889#appliesto.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>

Cannot generate SSPI Context

I'm running Server 2003 (with Active Directory) and SQL Server 2000. I see
SQL for a while from a client computer but then seem to lose my connection.
At this point, if I try to connect I'll get the "Cannot generate SSPI
Context" error message. It seems like I'm fine as long as I keep Query
Analyzer open but as soon as I close it and try to re-open it I get the
error. This doesn't seem to happen from another computer which isn't even a
member of the same domain.
Most replies to similar posts suggest
http://support.microsoft.com/defaul...kb;en-us;811889 as a source
for a possible fix. Either my problem is different or I'm too dense get the
clues found in the article. I'm desperate for help. Can somebody who has
been helped by the referenced article tell me what changes they made that
solved the problem?If you are using WINS make sure that your WINS computers are configured
correctly. Also, verify that the client is pointing to a valid DNS server.
In some cases, a client can be configured to use an external DNS server
which can cause this problem. Use ipconfig /all to look at the configured
DNS servers.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Cannot Generate SSPI Context

Win Server 2003
SQL Server 2000 SP4
SharePoint Server

Win Server 2000 (Domain Controller / Active Drectory)

Clients: Win XP / Access XP

I have two clients that can access the SQL Server and one that can not. All clients can access SharePoint (SLQ Server Back end) and directories controlled by AD.

The one that cannot access the SQL Server errors: "Cannot Generate SSPI Context". I have verified:
1) date and time
2) occurances of Security.DLL (win/system32; program file/common files/AOL...; Service Pack Files/i386 (x2))
3) Not using cached credentials

we recently moved our network and did not change any settings on any of the computers. I read KB811889; those suggestions did not resolve the issue.

Please assist me in touble shooting this connection problem. Also, how does changing the SQL Server Port on the server affect SharePoints connection to its database?

Hi,

When NT authentication is used to connect to SQL Server, we use a security API called SSPI (Security Support Provider Interface). In general, it's a tool that allows for a "server" to verify (usually with an AD domain) that a "client" is the same as he/she claims to be. This is done with exchanging of security package via a secure connection. More details can be found in the MSDN (see Platform SDK: Authentication).

In order SSPI to be functioning normally, you need to follow the instructions at KB 811889, please go through them once again. In addition, the following post from Nan is very useful:

http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx

Regarding changing the port that SQL Server listens on: Microsoft has been given a standard port (1433) by the internet task force and by default, a Default SQL Server instance listens on it. Given that, the client APIs when detecting you are trying to connect to a "Default" instance automatically assume the port is 1433. Unlike this, Named SQL instances by default determine port dynamically - usually select an available one and if not set otherwise, every time upon startup attempt to reuse it. However, note that the client has no means of telling what the named instance port is or a modified default instance port is. The options are:

(1) Manually specify port at client (either via alias or via data source definition like Data Source=myserver,myport)

(2) Use discovery mechanism provided by SQL Browser (SQL 2005) or a special "listener" thread (in SQL 2000). The thread is "listening" on UDP port 1434 and upon request provides information about the settings of the SQL instances installed on the machine.

Note that if you change the port of a default instance, you cannot use the SQL Browser/Listener approach, because for default instances we do not make a roundtrip to Browser - for perf reasons. You could create an alias or specify port when connecting.

If you could avoid changing the default 1433 port of SQL Server, that'd be great. BTW - changing the port may be related to your SSPI errors.

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.