September 24, 2009 at 5:22 pm
The computer with SQL Server 2005 Express communicates correctly with the database using either Named Pipes or TCP/IP through the ODBC connection we use to link a different application.
The ODBC connection on the remote computer, however, only works with TCP; if I set it up with Named Pipes we get that standard "does not exist or access denied ..." Unfortunately, the application on either computer fails when the ODBC protocol is TCP instead of named pipes ... a "not associated with a trusted SQL connection" error.
Firewall is disabled on both computers.
Why can the software connect to the database locally using either TCP or NP, but remotely only using TCP/IP?
thx in advance; I'm stumped ...
September 24, 2009 at 6:44 pm
Is 'remote access' enabled for your SQL instance?
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure
Executing the above will display the configuration. Look for 'remote access' and see what the configured and run value is.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 25, 2009 at 6:47 am
Thank you for your response. I ran the query and here are the results; the config_value and run_value are both 1.
remote access0111
Please keep in mind that I can connect remotely through the ODBC connection using TCP/IP, but not Named Pipes. However, I can connect through ODBC on the host computer using either TCP/IP or Named Pipes. I don't know why the application should be affected by such a low-level issue (the protocol) but there shouldn't be any reason why I can't connect remotely using Named Pipes -- or is there?
Thx again and I'll look forward to any assistance you can provide.
September 25, 2009 at 9:11 am
BTW, the Named Pipes ODBC connection works remotely on my Server (SQL 2005 Workgroups) but doesn't on another Client's Server using 2005/WG Edition ... my test environment is Express ...
September 26, 2009 at 8:38 am
I'm going to keep throwing out more information in the hope that somehow a light will come on ...
When a successfully tested ODBC link is set to TCP/IP, the application returns the message "Login failed for user ". not associated with a trusted SQL connection ..." But when I can make an ODBC connection using Named Pipes the application works correctly. What are the security-related differences between an ODBC connection using Named Pipes, and TCP/IP?
thx much for any advice you can provide ...
September 26, 2009 at 12:23 pm
When you create the ODBC connection what authentication type are you using? What authentication mode is the SQL Server you are connecting to in?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 26, 2009 at 12:31 pm
What are the security-related differences between an ODBC connection using Named Pipes, and TCP/IP?
TCP/IP Sockets
TCP/IP Sockets is a non-authenticated protocol. This means that connections are not authenticated, and connections and access levels are determined only by the user name and password provided during the connection. TCP/IP Sockets requires more administration than Named Pipes, since each user access permission must be defined at the SQL Server.
TCP/IP Sockets is a good choice for network protocol if your application does not require SQL Server's integrated security.
Named Pipes
Named Pipes is an authenticated protocol. This means that any time a user attempts to open a connection to the SQL Server via Named Pipes, the Windows NT authentication process occurs. This is important to remember because Named Pipes is also the default protocol when installing SQL Server. Named Pipes is required to support SQL Server's integrated security option. It's also worth noting that Named Pipes connects very fast – much faster than the other protocols.
Named Pipes is an excellent choice for network protocol if your application intends to use SQL Server's integrated security and does not need other protocols for remote connections.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 26, 2009 at 12:52 pm
SQL Server Authentication, using SA and the SA Password, on the remote computer. The SQL Server Instance is setup with Mixed Mode Security.
September 26, 2009 at 1:02 pm
Named Pipes
Named Pipes is an authenticated protocol. This means that any time a user attempts to open a connection to the SQL Server via Named Pipes, the Windows NT authentication process occurs.
That is helpful and interesting; thank you. I now see one reason why I can connect using TCP/IP, but not Named Pipes. Can you provide more information on how to troubleshoot the NT authentication process as it relates to accessing the SQL DB remotely through ODBC?
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy