December 5, 2007 at 5:55 pm
Hi,
Not sure if this is a connectivity issue of a DTA utility issue but here goes...
I am experiencing a Named Pipes Provider error 40 message as detailed below. I get this when I'm running the dta utility on a production server remoting onto a test server to use the metadata and optimizer there.
The error reported is...
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
(note: there is no (Microsoft SQL Server, Error: xxx) reported at the end of the error string)
The error results from the following cmdline syntax run from the production server.
dta -A 0 -E -e C:\TuningLog.xml -F -ix C:\DTA.xml -N OFF -of C:\outputdta.sql -rl ALL -s DTA_20071205
Prod box: 64bit SQL2005 SP2 Std Ed Named instance, TCP\IP and Named Pipes enabled, SQL Browser service running.
Test box: 32bit SQL2005 SP2 Std Ed Default instance, Listening for local and remote connections on TCP\IP and Named Pipes (configured through the SAC), SQL Browser service running. From the SQL Log on the test box I can see TCPIP is listening & Named pipes is listening on \\.\pipe\sql\query
There is no windows firewall configured on either box.
I can connect to the test server from the prod box using sqlcmd -E -S
I can ping the test server from the prod box
Would you have any idea what I'm doing incorrectly? The genericity of the error message doesn't give me much of a clue and the resolutions I've read all revolve around SQLBrowser, surface area config, and SQL Express which I've covered off. I've been through the MSDN protocol connectivity issues pages and nothing has helped. Bit stuck really.
many thanks
Matthew Eames
December 5, 2007 at 6:47 pm
u need to provode what all r&d you have aleady done... try this anyway....
create an odbc connection on prod box to test box using TCPIP, then try this command
try using sql authentication and see if that works?
are these two server on same domain?
have same login/password and what kinda access to windows user?
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply