Connection Strangeness
firebalrog
10.10.9.2 lvac03dmz
10.10.9.20 lvac09dmz
10.10.9.2 dmzserver
We can connect to the database (same on both .2,.20 getting ready for
migrating to new server we want to change the alias dmzserver in the
future to .20) using any of the following in the connection string
10.10.9.2\sqlexpress, lvac03dmz\sqlexpress, 10.10.9.20\sqlexpress,
lvac09dmz\sqlexpress
it will not connect using
dmzserver\sqlexpress
Also on some workstations all of the above works. only on a couple of
4 xp workstations does it fail.
we have also tried adding cname to dns for dmzserver but the ipconfig /
displaydns shows a ttl consistant with it getting the ip from the
hosts file and not from the dns server.
Ping all of the hosts works fine with a 126ms response. (clients are
on different subnets)
We also did ipconfig /flushdns which made no difference
Any ideas would be greatly appreciated.
Erland Sommarskog
> We have the following in the hosts file
> 10.10.9.2 lvac03dmz
> 10.10.9.20 lvac09dmz
> 10.10.9.2 dmzserver
>
> We can connect to the database (same on both .2,.20 getting ready for
> migrating to new server we want to change the alias dmzserver in the
> future to .20) using any of the following in the connection string
> 10.10.9.2\sqlexpress, lvac03dmz\sqlexpress, 10.10.9.20\sqlexpress,
> lvac09dmz\sqlexpress
>
> it will not connect using
> dmzserver\sqlexpress
And the error message is?
> Also on some workstations all of the above works. only on a couple of
> 4 xp workstations does it fail.
Which version of SQL Server? What client software is there on the client?
One thing I can think of is that there are SQL Server specific alias
point in the wrong direction. Run CLICONFG.EXE on the problematic machines
and see what you find.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
firebalrog
At home now but as I recall the error message was a login timeout.
I will check the client versions working vs not working when I get
back in and will also try cliconfg.exe
But what is interesting is the fact that it connects to both machines
when using the specific IP or the true server name but fails when you
use the dmzserver alias that is in hosts or as a cname in the dns
server. Perhaps there was something fixed in the sqlclient that
changes this behavior.
Thanks
Erland Sommarskog
> At home now but as I recall the error message was a login timeout.
This is an important point. If you get something like "timeout during
login", this may indicate that you make contact, but authentication is
not able to get through. In that case, the DNS for the server is not
a problem. But the reverse DNS from the server back to the workstations
may be.
Also, if possible use SQLCMD or OSQL to connect to the server to get
the error message, in case the application just produces a generic
error message.
> But what is interesting is the fact that it connects to both machines
> when using the specific IP or the true server name but fails when you
> use the dmzserver alias that is in hosts or as a cname in the dns
> server.
You would certainly get this behaviour if someone has set up an
alias in CLICONFG, as this overrides anything else.
firebalrog
Well cliconfig shows working computer is identical to the ones that
can only connect by the actual computer name or IP. No alias setup,
all dll version numbers and dates are identical.
Just in case it was unclear dmzserver is an additional entry in the
hosts file or as a cname in the dns server. On some computers if you
use dmzserver as the server name in the connection string it will
connect and on some others it still will not connect. But the IP is
identical to the working host entries or A records in the dns server.
The application works on all computers if you use lvac03dmz but will
not work on some of the computers if you use dmzserver.
Is it possible that there is some other windows dll at play here that
is not related directly to the sql native client?
All of the workstations are XP with all security patches and sp3
installed.
Erland Sommarskog
Do you have OSQL or SQLCMD on the clients so that you can test
connecting from them? It would be interesting to see a raw error
message from the API.
Or exactly what error message does your application produce?
Also, check the SQL Server error log, if there are any message about
login failures that are related to these connection failures.
You need to establish exactly where the connection fails. There are a
couple of possibilities.
firebalrog
Exact error message is as follows.
Unhandled Execption has occurred....
A network-related or instance-specific error occurred while
establishing a connection to sqlserver the server was not found ow was
not accessible. Verify that the instance name is correct and that sql
server is configured to allow remote connections (Provider: Named
pipes)
Provider Error 40- Could not open a connection to sql server).
Erland Sommarskog
SQL Server.
Since PING seems to work, this means they cannot be confused about
where the machine is located. (But doublecheck that PING DMZSERVER
actually gives the correct IP address.)
The one thing I could guess on is some network restriction. Maybe there is
some funny Active Directory setting which bans contact to DMZSERVER
for these machines? (But what I know about Active Directory fits on a
fingernail - and leaves most of the nail empty.)
The error message says Named Pipes, could that be a clue? Then again,
I think Named Pipes usually appears in these error messages, because
it is the last attempt.
Did you try connecting directly on the IP address to test the effect
of that?
Rick Byham, MSFT
use the dmzserver alias"
connect with named pipes? Try forcing a tcp connection by connecting to
"tcp:server\instance"
Does it work then? Perhaps this is a named pipes specific problem.
--
Rick Byham, MSFT
(Implies no warranty or rights)
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9D6EF2892...@127.0.0.1...
firebalrog
dmzserver all pinging works. All pings regardless of address or
address methodology resolve in 126ms or less on the t1 point to
point.
We will try forcing tcp:x\sqlexpress next I have also directed one of
the programmers to capture the execption information and post it here
once we have it caught. That might also help for diagnosis.
firebalrog
System.Data.SqlClient.SqlException: A network-related or instance-
specific error occurred while establishing a connection to SQL Server.
The server was not found or was not accessible. Verify that the
instance name is correct and that SQL Server is configured to allow
remote connections. (provider: Named Pipes Provider, error: 40 - Could
not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo,
SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout,
Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
integratedSecurity, SqlConnection owningObject)
at
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
timerExpire, SqlConnection owningObject)
at
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
host, String newPassword, Boolean redirectedUserInstance,
SqlConnection owningObject, SqlConnectionString connectionOptions,
Int64 timerStart)
at
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection
connection, ConnectionState& originalState)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables,
Int32 startRecord, Int32 maxRecords, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at
LVACID.dsCardsTypeTableAdapters.GetCardsTypeTableAdapter.Fill(GetCardsTypeDataTable
dataTable) in C:\CurrentLVAC\MembershipNet\trunk\LVACPhoto\LVACID
\dsCardsType.Designer.cs:line 778
at LVACID.frmID.frmID_Load(Object sender, EventArgs e) in C:
\CurrentLVAC\MembershipNet\trunk\LVACPhoto\LVACID\frmID.cs:line 777
firebalrog
tcp:dmzserver\sqlexpress still didn't work.
firebalrog
hint maybe?
tcp:lvac03dmz/sqlexpress didn't work also so it looks like it may be
using named pipes.
I will look at the router config but I believe that it is already
allowing 1434 through.
firebalrog
Erland Sommarskog
> lvac03dmz\sqlexpress does work without the tcp: in front of it.
Interesting. Now force TCP from one of the machines that can use
the DMZSERVER name. If they also fail on TCP, there is probably some
permissions issue with regards to the path for the named pipe. (I know
nothing about that. :-(
Did you check the network configuration for SQL Server? You can review
this in SQL Configuration Manager under Server Protocols. But a caveat:
if TCP is enabled, but it was enabled after SQL Server last was started,
it has not taken effect yet.
firebalrog
All protocols are enabled on the sqlserver and the firewall is off.
I will crack open the cisco routers and see what their settings are.
The working computers do not route through the same cisco routers,
they connect via vpn which I should have thought of before. Problem
probably is that something for tcp is blocked so it is using named
pipes but dmzserver doesn't work because it is not in wins.
Thank you for your help in diagnosing the problem you put a lot of
effort in helping. Once I fix the routers we will be able to move the
sql server database to the new server.
Paul
Rick Byham, MSFT
confused. Consider disabling the one you aren't using. That is, you might be
able to ping with v4, but then fail to connect with v6.
--
Rick Byham, MSFT
(Implies no warranty or rights)
"firebalrog" <barret...@gmail.com> wrote in message
news:f59bc77e-5bdb-438a...@24g2000yqy.googlegroups.com...
firebalrog
some reason and if we put np: in front of the hostname things connect
instantly but if we use tcp: it doesn't work at all.
I am not exactly sure what is wrong with the ciscos because they do
show 1434 udp permit and 1433 tcp permit to the subnet where the
server is. Also sqlcmd tcp:servername\sqlexpress works from another
computer in the dmz. so it appears that tcp is ok on the sqlserver and
just not getting through the routers.
Erland Sommarskog
I don't envy you.