I have been trying to configure a linked server to AD and have found plenty of write ups on how to do it, but have had zero luck with getting it to work. I also read that the ability was removed after SQL 2008; is that correct? I am running 2014 and am seeing the following error trying to expand the tree:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)...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). URL....Then the following error when trying to query:
SELECT * FROM OpenQuery(ADSI, 'SELECT displayName FROM ''LDAP://<DOMAIN>/DC=DOMAIN,DC=<DOMAIN>'' WHERE objectCategory=''User'' ')
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT displayName FROM 'LDAP://----/DC=----,DC=----' WHERE objectCategory='User' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
The linked server is set for 'Be made by the login's current security context' and local to remote is configured SA to a domain account we use for lookups. It does not seem to matter if I am logged in to the server with SQL or Windows credentials.
Need to set up a linked server in two node SQL Cluster pointing to a standalone SQL thru security setting "be made using current login's security context". It's double hop Kerberos setup. Cluster uses a domain account, so we manually set SPNs for that account with both instance name and protocol as well (set up these SPNs with SQL virtual name only).
Also, constrained delegation has been set to that stand alone SQL services (MSSQLSvc).
Both SQL cluster and standalone servers are in same domain, cluster service account is domain account and standalone SQL uses default SQL local service account.
Coming to SQL, when I create linked server, sometimes it lets me create without issues and sometime it throws this below warning and even if I create it won't work.
Login failed for user 'NT AUTHORITY/ANANOMOUS LOGIN'.
I am attempting to reach some Clipper tables through a 32-bit ODBC driver from a 64 bit SQL Server. As there is no 64 bit driver offered for Clipper, I am pursuing a solution similar to the one described here:
Creating a Linked Server with 64 bit SQL Server 2008 to MS Access
It involves using a SQL Express 32 bit instance as a bridge.
I have created a Linked Server on the 32 bit instance MTESTXPRESS as follows:
EXEC sp_addlinkedserver @server = N'ABDATA', @srvproduct=N'DataDirect 4.1', @provider=N'MSDASQL', @datasrc=N'ABServerCA' On the 64 bit instance ALISTESTER I have another Linked Server as follow: EXEC sp_addlinkedserver @server = N'ABACUS', @provider=N'SQLNCLI', @datasrc=N'ALISTESTERMTESTEXPRESS'
The suggestion is to then use a select statement such as:
SELECT * FROM OPENQUERY(ABACUS, 'SELECT COUNT(*) FROM ABDATA...ABBATCH')
Unfortunately, the DataDirect driver for MTESTEXPRESS will not recognize the 'ABDATA...ABBATCH' 3-part naming convention. The error message is:
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "ABDATA"
Is there some other way to select from the MTESTEXPRESS linked server?
We get the below error while performing a distributed transaction on linked server. We have several linked servers configured in the source server and all of them succeed with the distributed transaction except on one.
We did all the basic troubleshooting and moreover the distributed transactions work fine if we use a remote server instead.
Error: OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 3 The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" was unable to begin a distributed transaction.
Test code: begin distributed transaction select top 10 * from [SERVERNAME.REDMOND.CORP.MICROSOFT.COM].master.sys.objects ROLLBACK
Source server : Microsoft SQL Server 2008 (RTM) - 10.0.1779.0 (X64) Nov 12 2008 12:10:04 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)
Target server : Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
I've successfully created a Linked Server that connects a local DB Engine with another DB Engine through an ip over an extranet. I am able to run simple Select statement queries on the Local DB Engine and get results from the linked server. However when attempting to perform more complex queries that join tables from the linked server with tables from the local DB server, I get the following error message after several minutes of execution:
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Protocol error in TDS stream". OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
How I can resolve it. I've read on Distributed Transactions but I understand that it only applies to manipulation statements?
Both are SQL servers. Linked Server is SQL2008R2 if not mistaken. Local DB Engine is SQL2014.
I have a user who is trying to run a job (call an Stored Procedure) which connects to a Linked Server. He can run it OK using EXEC SP_Name but when he runs from the SQL Jobs it gives him the error: Linked servers cannot be used under impersonation without a mapping for the impersonated login.[SQLSTATE 42000] (Error 7437). The step failed.The Linked Server was setup using another account. Would this be fixed if I add the new user to the Security section of Linked Server without breaking the current configuration?
Hi , On my Desktop i registered Production Server in Enterprise Manager on that Server if i go to SecurityLinked Servers There is another Server is already mapped, when i am trying to see the Tables under that one of the Linked Server i am getting the Error message saying that "Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems why i am not able to see from my Desk top i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem What might the Problem how can i see the Tables in Linked Server from my DESKTOP
I'm new to SQL. I have a scenario, Where customer want to move all the jobs from original SQL server to some remote SQL server and want to trigger jobs on remote server to do its work on original server.
I want to schedule a job which pulls files from a non SQL server (Sybase) which later needs to have a step 2 kicking the ssis package. Problem is that, on the source a batch file will run every 4 hours and outputs total of 10 text files. (takes 5 minutes complete). Now, on destination i want to pull these files via SQL job but while scheduling;
1. I don't see any option saying like 4 hours 10 minutes or so 2. If its out there, then i believe this might be a problem as this time would be an increment one e.g next run would be 4 hours 20 minutes in that case.
How should i achieve pulling these files up because we have an SSIS package on destination that needs those text files to be used as soon as they arrive on SQL server(destination)
The SQL Server Agent (MSSQLSERVER) service on server started and then stopped. If i Manually start the services also SQL Server Agent services are stopping automatically.Though I selected Auto Restart SQL Server Agent if it stops unexpectedly, The services are stopping automatically.TCP/IP protocol was enabled for port 1433.Are there any setup I missed?
I want to create a database on my local machine and work with it. But I am not able to see the name of my local server under "Database Engine" in the list of "Registered Servers".
Options I tried:
(1) In Registered Servers window, On Database Engine, I right clicked and in that selected "Update Local Server Registration"
(2) In Registered Servers window, On Database Engine, I right clicked and in that selected "New" -> Server Registration. In "New Server Registration" Window, in "Server Name" drop down I clicked "Browse for more" but still in "Browse for Servers" window, I am not able to see the name of my local server in "Local Servers" tag under Database Engine.
(3) If I do File -> Connect Object Explorer -> Connect to Server window in "Server name" drop down box, if I click "Browse for more", I am able to see local server name eg. "XYZ" under Analysis Services, Reporting Services, and Integration Services but not under "Database Engine"
Please help me create a new database engine in SQL Server 2005 or list my local server under "Database engine" ?
Hi everybody. I was trying to install MS SQL Server Engine this morning. At the command prompt I typed setup SAPWD=password SECURITYMODE=SQL (where password was my password). After running the MSI I got the following error: Product: Microsoft SQL Server Desktop Engine -- Setup failed to configure the server. Refer to the server error logs and setup error logs for more information.The Event ID is 1013. Can anybody tell me how to install this program. Thanks in advance for any help.
hi,I'm building a simple search engine using keyword and title of a page:select ID, content_ID, 3 as 'weight'from tblPagewhere keywords LIKE '%test%'unionselect ID, content_ID, 2from tblPagewhere title LIKE '%test%'now I want to sum and order 'weight'thx
Using the SQL Server Everywhere CTP, when I create multiple SqlCeConnections using the same data file, do they each open the file, or is there some sort of engine component running in the process that they each use?
What's new with NUMA in SQL Server? Is there any way to get a reading, from SQL Server or perfmon or hardware vendor tools or VM tools, of how much you are crossing NUMA boundaries, and what that is costing? The only "NUMA management" trick I know is setting maxdop at or below the number of cores on a physical processor. I guess the rest is supposed to be automagic.Is there anything else one can see or do regarding NUMA?
I have a problem upgrading a McAfee ePolicy Orchestrator which uses SQL Server 2008 R2 SP2. McAfee logs indicate that server names from SQL server and from McAfee installer does not match.It seems that the reason is that machine name was changed after SQL Server was installed.
MSDN instructions for sql server name changing give next commands to solve this problem: SELECT @@SERVERNAME AS 'Server Name'; sp_dropserver <old_nameinstancename>; GO sp_addserver <new_nameinstancename>, local; GO
But it didn't work for me, because "SELECT @@SERVERNAME AS 'Server Name'" shows correct (actual) name of sql server. So sp_dropserver says that there is now such instance.In the same time if I use this commands:
Server consolidation is the word of the day here. One of the elements is to use, when applicable, virtual machines in our devstageprod environment. Our sql servers (with local storage) are well utilized and run fine. I don't want to move the entire server (sql 2005 engine + data) into a VM as I think performance will be bad. But, per my question, if only the sql engine is running in a VM while the data is stored on our SAN, I think we may achieve acceptable performance.
Do you have any thought or experience with this mix of Vm and SAN from a sql perspective?
i want to ask why database i cannot back up or expand if i make expand the database the message showing "the database (database name) is not accessible. (object explorer)before the database is working fine,
I have SQL 2008 R2 version. The server has enough disk space where the SQL is running. But the log file is not refreshed. in other words a new sql server log file does not create a new file if the old one is full.
I have a few questions about the SQL Server Desktop Engine (MSDE), sorry there are so many ..
1. Which software products does this come with ? 2. If I create a SQL Server Database and want to distribute it to clients, how do I actually do it with MDSE ? 3. If I need to "upgrade" my database at some point in the future (that may contain data at a client site) how would I go about getting all the data out - would I need to write an external app ? 4. What are it's pros and cons when compared to installing full SQL Server on a client site ? 5. I've heard that the preformance of an MSDE Database degrades severely after more than 5 concurrent connections, is this just due to the performance of the product or is it something to do with the licencing model ? 6. Is it possible to write applications in, say VB, to alter the database once it has been installed on client site e.g. modify/create tables, indexes, stored procedures ?
SQL Server 2005 SP2 (build 3054)Consider the following scenario:- A complex multi-statement table valued function is created. Let's callit dbo.tfFunc(@Param1, @Param2)- A SELECT statement is executed, that calls the above function twice,each time with a different set of parameters. In pseudocode:SELECT <column list>FROM dbo.tfFunc(1, 2) AS f1<some JOIN operatordbo.tfFunc(3, 4) AS f2ON f1.col = f2.colINNER JOIN dbo.Table1 AS t1ON ...etc.The exact statement is probably irrelevant, as long as the same table-valued function is called twice (I have observed the issue in two verydifferent statements calling the same function). The statement isexecuted in a SNAPSHOT isolation level transaction, although this mayalso be irrelevant.- The statement continues executing for a long time. If sp_who2 is run atthat time, the following row is returned for the statement connection(only relevant columns are shown):SPIDStatusBlkByCommandCPUTimeDiskIOLastBatch63 SUSPENDED63SELECT2928268308/31 18:17:37The statement appears to be blocked by itself. If sp_lock is run at thattime, the following rows are returned:spiddbidObjIdIndIdTypeResourceModeStatus63213166246410TABSch-SGRANT63213166246410TABSch-MWAITIt appears that SQL Server waits indefinitely trying to obtain a schema-modification lock on a resource which already has a schema-stability lockplaced on it by the same connection.The following is pure speculation, but it seems reasonable to assume thatthe server has materialized the result of the first call to the functionusing a temporary table in tempdb, and is trying to materialize theresult of the second call using the same temporary table (same ObjId insp_lock results).I do not know why this does not cause a deadlock error.Unfortunately, I do not have a simple repro script for this. The actualcode is rather complex. While I can devise a workaround, this does looklike a bug. I am posting it here before submitting a bug on Connect, incase anyone can shed some light. Thanks.--remove a 9 to reply by email
I am restoring a backup of sql server 2005 which I inherited on to sql server 2012 and is in a recovery pending state and the reason why is, this is a backup from a different domain and does not have the logins from the legacy domain, looks like its some access issue as its not being able to find the necessary login on sql server 2012.
I try to find some feedback regarding setting the TempDB files on a RAM disk.Specifically I am looking for "production results" that could show the difference/benefit of such an usage.The tests on physical server and VM I already made have shown a boost in overall SQL Server 2012 performance on SQL Server instances housing data for SharePoint 2013 and Dynamics AX 2012 R2.Graphic below show differences between 5 different configuration on the same physical server:
- Physical HD: Server with local HD - Physical SANEX1PRD: Server with TempDB files stored on a low-end SAN - Physical SAN1: Server with TempDB files stored on a high-end SAN (around 100000 IOps) - Physical SAN1 Jumbo: same setup with Jumbo Frame activated on NIC and DB engine - Physical RAMdrive: with TempDB files stored on a 16 GB soft RAM drive within OS memory
Results were really impressive for the DB engine housing Dynamics AX data. My colleagues from the SharePoint team told me it also boosted a bit overall SharePoint performances but they did not have any baseline comparison to show.If you have some feedback, results, links, whatever I am interested.Indeed before setting this to all our SQL Server 2012 instances I y rather collect some *real world* feedback.
I am using SQL Server Management Studio 12 to try to restore a backup to a new database. I know I have done this before, but cannot seem to make it happen now. Her is what I have.
Backup file is "C:TempInProcessInspection_DataSQL_backup_2015_02_23_000012_4551393.bak" and it is for sure there.
This came from a customer of mine.I launch SQL Server Management Studio 12 and log in as SA. I right click on Databases and chose Restore Database. I choose Device then file then Add and try to browse the the named location.
When I get there, regardless if I choose All files or Bak, I see no files.I tried to upload an image of the "Upload Backup File" dialog, but MSDN would let it happen. When I get to the c:temp folder, no files appear in the window.
