August 22, 2013 at 3:58 pm
Hello all,
I have a query to AD that worked 2 days ago, but did not work last night.
SELECT sn, givenname, sAMAccountName, mail, displayName
FROM openquery(ADSI,'
SELECT givenName, sn, employeeID, mail, sAMAccountName, displayName
FROM ''LDAP://OU=DedicatedRemoteSites,DC=berkshire,DC=loc''
WHERE objectCategory = ''Person'' and objectClass = ''user'' and mail = ''*.org'' and givenName = ''*''
');
The message I get is this:
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".
The only thing that I am aware of that did change is that we had to change our main Administrator Password.
Is there something related to this that I need to update somewhere?
Thank you in advance for your help.
August 23, 2013 at 7:17 am
How was the linked server ADSI configured?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 23, 2013 at 7:22 am
Did you setup the linked server using the main administrator credentials? Can you test is the linked server is working at this moment?
August 23, 2013 at 7:36 am
If this is a critical process, consider using MASTER.sys.sp_testlinkedserver @ServerName to check the availability of the server before you run your process(es). You could make a function or procedure like: CREATE PROCEDURE [dbo].[utl_dba_TestLinkedServerAvailability]
(
@Server sysname
)
AS
DECLARE @EmailAddress varchar(75) SET @EmailAddress = 'dba_alerts@yourcompany.com'
DECLARE @EmailBody NVARCHAR(max)
DECLARE @EmailSubject NVARCHAR(140)
DECLARE @Env VARCHAR(65)
SET @Env = @@SERVERNAME
BEGIN TRY
EXEC MASTER.sys.sp_testlinkedserver @Server;
END TRY
BEGIN CATCH
RAISERROR ('THERE WAS AN ERROR', 16, 1)
SET @EmailSubject = '' + @Env + ' - UNABLE TO QUERY LINKED SERVER: '+ CONVERT(VARCHAR(19),GETDATE(),121)
SET @EmailBody = '*********************************************************************************************' + CHAR(13) +
'THERE WAS AN ERROR CONNECTING TO ' + CAST(@Server as varchar(25)) + ' :: PLEASE CHECK CREDENTIALS!' + CHAR(13) +
'*********************************************************************************************' + CHAR(13)
EXECUTE msdb.dbo.sp_send_dbmail @recipients = @EmailAddress
,@subject=@EmailSubject
,@body = @emailBody
,@body_format = 'TEXT'
,@sensitivity = 'Confidential'
,@importance='High'
END CATCH
GO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 23, 2013 at 10:17 am
I went in on my sql server today to manually copy some tables to get me by.
I was using the import & export data tool.
I could select my source server(another virtual server on same domain), database, my destination server(server I am logged into), database.
Clicked copy data from one or more tables or views, clicked Next.
When it tries to pull up table list I get
Protocol Error in TDS Stream listed 4 times.
in additional information I get "Protocol error in TDS Stream" 3 times then
"Protocol error in TDS Stream(Microsoft SQL Server Native Client 10.0)
August 23, 2013 at 10:45 am
I don't recall at this time, that was setup well over a year ago.
How would I test this?
HanShi (8/23/2013)
Did you setup the linked server using the main administrator credentials? Can you test is the linked server is working at this moment?
August 23, 2013 at 11:07 am
See my previous post...
EXEC MASTER.sys.sp_testlinkedserver @Server;
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 23, 2013 at 1:05 pm
EXEC MASTER.sys.sp_testlinkedserver ADSI
SELECT sn, givenname, sAMAccountName, mail, displayName
FROM openquery(ADSI,'
select givenName, sn, employeeID, mail, sAMAccountName, displayName from ''LDAP://OU=CanaanCampus,DC=berkshire,DC=loc''
where objectCategory = ''Person'' and objectClass = ''user'' and mail = ''*.org'' and givenName = ''*''
');
gives this:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "
select givenName, sn, employeeID, mail, sAMAccountName, displayName from 'LDAP://OU=CanaanCampus,DC=berkshire,DC=loc'
where objectCategory = 'Person' and objectClass = 'user' and mail = '*.org' and givenName = '*'
" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
August 23, 2013 at 1:32 pm
Don't forget the quotes...
EXEC MASTER.sys.sp_testlinkedserver 'ADSI'
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 23, 2013 at 1:57 pm
No change, same error message.
August 23, 2013 at 2:50 pm
My bad, no quotes as the parameter is of type sysname.
Click on the plus sing near the linked server (in the SSMS GUI)
Expand it, click catalogs...
If it throws an error from there, the security change broke your security settings set in the linked server set up. Fixed the account/password that was used to set up the linked server in the first place.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 23, 2013 at 2:55 pm
I can drill down until I hit the tables, when I click the plus on tables I get this:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "ADSDSOObject" for linked server "ADSI". (Microsoft SQL Server, Error: 7301)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=7301&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
do I need to delete the linked server and re-create using the account with new password?
August 23, 2013 at 2:58 pm
Its not just quering AD though, I am getting "Protocol error in TDS Stream" if I use the import wizard to copy tables from another server.
People are reporting errors when trying to use Crystal reports on it.
Any ideas, my head hurts from banging it on the desk - LOL 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply