Access Denied using MSIDXS / Linked Server
-
Using SQL 2000 running on W2K Server SP4 all latest patches.
The SQL and SQLServerAgent services are running under a domain account that has domain admin priviledges.
I am trying to query MSDIXS on a different server (running 2003 Server SP1 configured as a DC) using Windows Authentication. The Index Service is running under the default LocalSystem account. (I have also tried running it under the same account as SQL Server)
I created the Linked server on the SQL server using:
EXEC sp_addlinkedserver DescriptiveName,
'Index Server',
'MSIDXS',
‘CatalogName'
I can run the following query from QueryAnalyzer (or in a stored procedure) connecting using Windows Authentication on the SQL Server without problem:
SELECT * FROM OPENQUERY(DescriptiveName, 'SELECT FileName, Path, VPath FROM RemoteServer.CatalogName..SCOPE()')
However if I try to execute this from QA on any other system using Windows Authentication (or from a stored procedure), logged in as same user as while on the SQL server, I get the following error:
OLE DB provider 'MSIDXS' reported an error. Access denied.
[OLE/DB provider returned message: Invalid catalog name 'CatalogName'. SQLSTATE=42000 ]
OLE DB error trace [OLE/DB Provider 'MSIDXS' ICommandPrepare:
repare returned 0x80070005: Access denied.].If I turn
OLE DB provider 'MSIDXS' reported an error. Access denied.
OLE DB error trace [Non-interface error: OLE DB provider MSIDXS returned DBPROP_STRUCTUREDSTORAGE without DBPROPVAL_OO_BLOB being supported].
[OLE/DB provider returned message: Invalid catalog name ‘CatalogName’. SQLSTATE=42000 ]
OLE DB error trace [OLE/DB Provider 'MSIDXS' ICommandPrepare:
repare returned 0x80070005: Access denied.].If I change the query to access an Index Server on another W2k server in the domain (using Windows Authentication), with the Index Server service also set to the default localsystem account, it works without problem (from all systems). The issue is ONLY with this one W2K3 Server, and only when I try to execute the query from any system OTHER than the SQL server and ONLY when using Windows Authentication.
Also, I can query the remote Index Server from a client using .Net and an ADOB connection, but I need to execute this on the SQL server as we need to join/filter the resulting dataset with other SQL tables.
The catalog name is correct (or it would not work from the SQL Server), so it appears to be an access issue, but I cannot determine what I need to change to the Linked Server settings so that this will run within a stored procedure, using Windows Authentication.
I have modified the linked server Security tab, trying all available options and providing a domain admin account for impersonation or remote login, all resulting in the same error.
I know this can work, as I have seen a number of references to it and it I can query any other remote Index Server on the LAN – just not this one (the only one running W2k3). Can anyone tell me what settings are required to enable this to be executed on any system other than the SQL server, using Windows Authentication?
-
Tim,
It all may depend on a lot of things but take a look at this article:
Troubleshooting Kerberos Delegation
http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx
This is sort of a White Papaer on delegation. Once on the page, scroll down to the paragraph:
"Access as null user"
and see the picture right above this subtitle. This is the first Authentication scenario, there are some more. So the article shows how the clients can connect to the first server but could not be authenticated on the second server.
See if any of the scenarios are relevant to your setup. Why it is only Winodws 2003 server that don't work? Because the security was changed from 2000 to 2003.
P.S. Account has to be trusted for delegation.
Also see:
Connecting to a 3rd Computer-Delegation
Regards,Yelena Varsha
-
Thank you for the information - I have been out of town, hence the delay in my reply.
I reviewed the info from the two urls you provided. Most of the information seems to related to IIS delegation, which is not being utilized in our scenario.
As for potential delegation issues - I have tried enabling delegation for both the SQL server as well as the accounts being tested as mentioned in the articles, without any change in the problem. There are no errors in the event log and everything else appears to function properly, so I do not think this is the cause (but am not sure).
If it was possible to force a stored procedure to use a specific SQL login, then I could hard code this one sProc to use that connection.
Since I'm at a loss on how to properly diagnose the issue, much less resolve it, I will be contacting MS shortly.
-
Yelena's documentation reference are what you need to look at to set up the supporting structure for Kerberos delegation. Without it, you can't use Windows authentication. I'll explain.
Windows NT used NT Lan Manager (NTLM) for an authentication mechanism. NTLM allows only a single hop. For instance, from the client to the SQL Server. The second hop is not allowed. The connection from the SQL Server to the remote Index Server would be the second hop. As a result, Microsoft used Kerberos as the primary authentication mechanism under Active Directory.
Kerberos only allows one hop by default. However, it does have the ability to permit delegation (basically similar to resubmitting the user credentials) so you can have more than one hop. For instance client -> SQL Server -> Index Server.
In order to do so you need to do a few things. First, the user account making the connection must not be set where it cannot be delgated.
Second, the SQL Server needs to have its SPN registered properly in Active Directory. This may also need to be done under the service account SQL Server runs under.
Third, the service account SQL Server runs under must be trusted for delegation.
Fourth, the computer account SQL Server is running on must be configured to allow delegation. Under Windows Server 2003 Active Directory there is constrained delegation in which case what is doing the delegation must be set (SQL Server).
There may need to be an SPN setting for the Index Server. I'm not sure on this one. But if SQL Server is your last stop, the SPN has to be there... the question is whether or not HOST/server and HOST/server.fqdn.com is sufficient to get delegation all the way through to Index Server or not.
If you're trying to troubleshoot, make sure you have kerbtray running on your client. It'll show you the Kerberos tickets you have. You should pick up one for SQL Server (if you don't, that would indicate a potential issue with the SPN). You should pick up one for the Index Server server as well.
K. Brian Kelley
@kbriankelley -
Thank you for the additional info. While I tried modifying the useraccount and server to allow delegation I did not add the two SPNs.
Since SQL logins work, I have temporarily modified the middle tier code to use a SQL Login for the connection for this search method, vs the default Windows Authentication. Once I get over the hump on this and get it out to testing, I will revisit it and try to get it to work 'properly'.
I was already accessing two other W2k servers without issue, so when I hit the wall with this 2003 server, I was reticent to modify too much / make changes as mentioned since my test systems do not totally mirror production.
Having lost several days trying to resolve this, the work around was a quick way to move forward.
Again, thanks to you and Yelena for taking the time to find and explain this. I will revisit it in the near future.
-
Dear All,
I have a similar scenario...
Using SQL 2000 running on Windows Server 2003.
The SQLServer and SQLServerAgent services are running under local system.
I am trying to query MSDIXS on a different windows 2003 server using Windows Authentication. The Index Service is running under the default local system account. These servers are NOT under the same domain.
I have used the hosts file to give me the capability of accessing the remote indexing server using the server name.
I can run the OpenQuery from QueryAnalyzer (or in a stored procedure) on the SQL Server without problem. I need to execute this on the SQL server as we need to join/filter the resulting dataset with other SQL tables.
When I try to run the query from a client web app I get access denied error.
OpenQuery:
SELECT *
FROM OPENQUERY(FileSystem,
'SELECT Filename,PATH,Rank,HitCount FROM ServerName.CatalogName..SCOPE() WHERE ( (FREETEXT(Contents, ''keyword search''))) ORDER BY Rank DESC')
Is it possible to implement this openquery search between client and server without a domain controller?
Tim,
"Since SQL logins work, I have temporarily modified the middle tier code to use a SQL Login for the connection for this search method, vs the default Windows Authentication. Once I get over the hump on this and get it out to testing, I will revisit it and try to get it to work 'properly'. "
I'm interested to know how you implemented this SQL Login for the connection and whether this approach would work for me in this scenario?
Any help would be appreciated!
Regards,
Nick
-
I never got this working with Windows Authentication. Our DCs are now a combination of 2003 and 2008 and I still have not figured out the Kereberos issue mentioned in the link above, so I continue to use a SQL logon account. Since we will be migrating to SQL 2008 end of this year, and likely will move to a third party index product vs MSIDXS, it wasn't worth the effort to resolve the WA issues. MSIDXS is ok, mostly because its free. I spent an inordinate amount of time trying to get the latest version of Windows Search to work from SQL 2000, but have yet to find anyone that has accomplished it. From what I have read on the MSDN site, it is not supported.
As to our implementation:
The SQL Logon account has sufficient rights to the database that I use in the query (linked/FKs). The 2003 server with MSIDXS is in the SQL linked server list using a domain admin account for the access.
The query itself is made via ADO.Net and passes the SQL account in the connection string (I use connection pooling for all other queries and Windows Authentication).
In the linked server dialog, I have the following settings:
Product Name: Index Server
Data source: MSIDXS
Location: (index name on the 2003 server- this is the actual name of the Index itself)
Security Tab:
select 'Be made using this security context'
enter the account and password with Admin rights to that server in the form domain\userID or machine\userID.
As to your question of will it work outside a domain...don't know, but I would think it should.
Before testing from your web app, make sure it is working from QA, logging in with the SQL Logon account.
-
Tim,
Many thanks for the swift reply. Unfortunately I still get the access denied error when trying to execute the stored procedure from any app other than QA on the SQL Server machine?
I can't seem to find any documentation that states whether you can implement this kind of openquery search if the sql server machine does not reside on the same domain as the remote indexing server? Is this possible?
To reiterate, I can execute the stored procedure that runs the openquery from the SQL Server box via QA but I cannot run the same stored procedure when executed either by my asp web app?
Thanks in advance,
Nick
-
Is your web application using the default account for anonymous queries (IIS_WPG in 2003/IIS6) or are you passing the username/password (of your SQL Login account) within your connection string? If you are not using a domain are the Web and SQL box in the same workgroup?
-
The web app passess a connect string to the stored proc that contains the SQL login account info.
The web and sql boxes are not on the same workgroup.
(Incidently, the web app is configured in IIS to allow anonymous connections but the communication between the web app and sql server is via the connect string as mentioned above so I don't think this matters?)
Hope that helps?
-
What error is being returned by the Web app? (copy paste the error text)
Is this an ASP.Net application? If not, what data query methodology are you using?
Are there other SQL queries that do work from the web app? (is this an issue with just this query or accessing SQL in general).
Are you passing the query from the web in the same form as in QA (ie both executing a stored procedure or both using an inline query)?
-
This is a legacy but robust classic ASP / VB6 application. VB6 objects created within the classic asp page do all of the sql data access firing stored procedures that populate ADODB.Recordset objects. All other SQL queries within stored procs work perfectly.
I have an instance of SQL Server running on my development laptop that is connected to my production sql server instance.
I can execute all stored procedures except from this distributed openquery.
Yet, I can execute the same stored proc from the SQL Server box via SQL QA without error?
The error message comes from the stored procedure and reads...
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSIDXS' reported an error. Access denied.
[OLE/DB provider returned message: Invalid catalog name 'PRESENTATION'. SQLSTATE=42000 ]
OLE DB error trace [OLE/DB Provider 'MSIDXS' ICommandPrepare::Prepare returned 0x80070005: Access denied.].
This error is produced within the web app vb6 dll at the line where the stored procedure is executed.
This is exactly the same error if I execute the stored proc remotely from SQL QA.
-
Unfortunately, I am not accessing this via a Web app, just a .Net Winforms application, so I'm afraid I cannot take this much further.
Im guessing you already did a google search on that error. Found the following related to the jet provider, so it may not be applicable to your case, but sounds similar:
-
Thanks for your help! There research must go on!
Regards,
Nick
-
I'm experiencing a similar problem with my setup. Basically I have two servers - server 1 is running Index Server and has a few catalogs setup and server 2 is running SQL Server 2005.
I understand you can use the Linked Server functionality to connect to Index Server however, I'm having problem with the connection. I've had this setup locally with index service and sql on the same box and it's worked fine, but not when they're on separate servers.
I think server 2 is under the assumption that the catalog is on the same box and not on server 1 even though I've specified the location using the server 1 IP address.
Can anyone point me in the direction of where I'm going wrong?
The error message is:-
OLE DB provider "MSIDXS" for linked server "TEST" returned message "There is no catalog. ".
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply