dba123
asked on
Connection Failed. SQLState: HYT00 SQLServer Error:0 TimeoutExpired
I have one server that contains SQL Databases and on that server, setup a SQL user account and gave that account rights to the master DB and a couple of others including db_owner rights and also EXEC permissions to a stored procedure xp_logininfo
This worked find for another server which tried to connect to this server's DBs when I tried to create ODBCs from the other server to this but now I have a 3rd server which when after I setup the security on the primary server, I can't get to the tables via ODBC.
When I try to create a standard System DSN on my 3rd server and try to get to the tables of the Primary Server's DB, I get the error:
Connection Failed. SQLState: HYT00 SQLServer Error:0 TimeoutExpired
I am not sure why, this NT Logon has the same permissions as another NT Logon has when trying to connect ot my initial Primary Server's DBs.
And it isn't the multiprotocal issue in the SQL Network Client Utility
Any clues? I also set the timeout to 100 seconds on both servers in Enterprise Manager. I was able to register the primary server in my 3rd Server's Enterprise manager so what else am I missing here?
This worked find for another server which tried to connect to this server's DBs when I tried to create ODBCs from the other server to this but now I have a 3rd server which when after I setup the security on the primary server, I can't get to the tables via ODBC.
When I try to create a standard System DSN on my 3rd server and try to get to the tables of the Primary Server's DB, I get the error:
Connection Failed. SQLState: HYT00 SQLServer Error:0 TimeoutExpired
I am not sure why, this NT Logon has the same permissions as another NT Logon has when trying to connect ot my initial Primary Server's DBs.
And it isn't the multiprotocal issue in the SQL Network Client Utility
Any clues? I also set the timeout to 100 seconds on both servers in Enterprise Manager. I was able to register the primary server in my 3rd Server's Enterprise manager so what else am I missing here?
Try using the profiler to see if the login is getting through to the server at all and what it is executing.
ASKER
thanks, will try
You have 3 possibilities:
1) do you habe the 2 computers connected? (try with ping---this just to eliminate a dummy possible error)
2) soem incompatibilities: describe what OS, what SQL, what SP are installed on the 3 servers (are they the same??)
3) Some things are not setted well...this will look at it only after eliminated the first 2...
good luck,
xenon
1) do you habe the 2 computers connected? (try with ping---this just to eliminate a dummy possible error)
2) soem incompatibilities: describe what OS, what SQL, what SP are installed on the 3 servers (are they the same??)
3) Some things are not setted well...this will look at it only after eliminated the first 2...
good luck,
xenon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I can ping
I am using Windows server 2000 just like my other 2 servers which can connect to each other fine
I am ussing SQL 2000
The latest SP for SQL 2000 is not installed on my 3rd server but is on my other 2 (I need to install that soon because of that latest vunerability of course)
I am using both TCP/IP and Named Pipes as is the same for my other 2 servers.
I am able to register my other server's database in this Server's SQL Server Enterprise manager
The settings on the target SQL Server that I am trying to connect to are the following"
The user is using NT Authentificaton
The user has db_owner rights to the databases in question that I am trying to connect to
The user has public rights to the master DB on that system (I even tried db_owner, makes no difference)
The user must use NT Authentification
The user has EXEC rights to the xp_logininfo stored procedure for the Master DB which it must have also
The user is set to having System Administrators Security roll in the properties of this user
I have noticed the following:
There is a delay when after I start to setup the ODBC (System DSN) at the point where you click next to allow you to select the tables. before it gets there, I either get a big delay and it does eventually get there, or I get a timeout and it doesn't get there, or I get the HTT00. If I try, sometimes it is different each time I go back and try clicking next. Sometimes if I get the HYT00, it goes ahead and I can get to my tables but sometimes it doesn't let me and sits in the section where you specifiy the authentification type for the ODBC I am setting up.
why I get mixed results and a delay? I have no idea. I have set the timeout for all 3 servers in SQL Server EM under Tools | Options | Advanced to 100 seconds
I am using Windows server 2000 just like my other 2 servers which can connect to each other fine
I am ussing SQL 2000
The latest SP for SQL 2000 is not installed on my 3rd server but is on my other 2 (I need to install that soon because of that latest vunerability of course)
I am using both TCP/IP and Named Pipes as is the same for my other 2 servers.
I am able to register my other server's database in this Server's SQL Server Enterprise manager
The settings on the target SQL Server that I am trying to connect to are the following"
The user is using NT Authentificaton
The user has db_owner rights to the databases in question that I am trying to connect to
The user has public rights to the master DB on that system (I even tried db_owner, makes no difference)
The user must use NT Authentification
The user has EXEC rights to the xp_logininfo stored procedure for the Master DB which it must have also
The user is set to having System Administrators Security roll in the properties of this user
I have noticed the following:
There is a delay when after I start to setup the ODBC (System DSN) at the point where you click next to allow you to select the tables. before it gets there, I either get a big delay and it does eventually get there, or I get a timeout and it doesn't get there, or I get the HTT00. If I try, sometimes it is different each time I go back and try clicking next. Sometimes if I get the HYT00, it goes ahead and I can get to my tables but sometimes it doesn't let me and sits in the section where you specifiy the authentification type for the ODBC I am setting up.
why I get mixed results and a delay? I have no idea. I have set the timeout for all 3 servers in SQL Server EM under Tools | Options | Advanced to 100 seconds
ASKER
I found out that it needed to be set to Named Pipes. One of my DSN connection was trying to use TCP/IP, as soon as I changed it to Named Pipes after changing the settings using the "Client Configuration" button in my DSN setup, it was fine.
thanks!
thanks!