Author |
Topic |
ccyre
Starting Member
15 Posts |
Posted - 2008-02-20 : 14:31:35
|
Hey there I am an SQL noob, our bank has no real SQL Admin, we had onw that left but never had a good knowledge transfer. We have 2 SQL servers clustered. IBSQL1 and IBSQL2 they make up cluster IBSQL. We have 2 other servers IBIIS1 ans IBIIS2 and we noticed when installing apps on them that they could not see the database. Yet it can ping IBSQL. The problem is on IBSQL1 and 2 port 1433 is not listening or open. Thus on IBSQL it isn't either of course. I have read of all kinds of people having this issue and most have said you need to manually add a connection string "Provider=sqloledb;Data Source=machineName,1433;Initial Catalog=xx;User ID=xx;Password=xx" Problem is I do not know how to add a connection string.I need step by step instructions since im new to this. Start/programs/etc..... Apparently it is not enough to open the properties of tcp/ip in the config util and say 1433. You need to reenforce it by adding that string.As it stands I cannot "telnet ibsql 1433" it tells me to take a hike basically.So is the connection string the likely cause? or could it be something simpler? Thanks in advance. |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-20 : 15:19:51
|
The connection string is for an application to know how to connect to the database server. Ignore what you've read for now.If you can't telnet to the database server via port 1433, then you've either got a network issue or the databas server is not listening on port 1433.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
ccyre
Starting Member
15 Posts |
Posted - 2008-02-20 : 15:28:13
|
Even though it is set to? Sounds like I need to break down and call Microsoft. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-20 : 17:49:40
|
I don't understand your question.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
ccyre
Starting Member
15 Posts |
Posted - 2008-02-20 : 19:22:56
|
you said "then you've either got a network issue or the databas server is not listening on port 1433"network not an issue and it should be listening to port 1433 because it is set to in the network config. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-20 : 19:47:20
|
If it is set to port 1433 according to server network utility, then it is a network issue. Contact your network admins for assistance. You must be able to telnet to the database server and port in order to connect to the SQL Server. Could you double-check the port though? Just because server network utility says 1433 doesn't mean that it is actually listening on it. Perhaps someone changed it and didn't restart the service, which is required for changes. What does the SQL Server Error Log show for the port?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
ccyre
Starting Member
15 Posts |
Posted - 2008-02-21 : 07:12:10
|
i verified, it is set to 1433, and where is the error log located? actual .log file or a button to view error log within enterprise manager? |
|
|
ccyre
Starting Member
15 Posts |
Posted - 2008-02-21 : 09:13:13
|
Could it be a patch from MS that caused this? On known good server (Win2K3 Standard SP2) auto updates is turned off so thats all it has is sp2. Bad server Win2K3 Enterprise R2 SP2 has all the latest patches. Was wondering if a patch might cause this. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-21 : 16:06:28
|
You can view sql server log under management in em. |
|
|
ccyre
Starting Member
15 Posts |
Posted - 2008-02-21 : 16:54:54
|
ok i looked and there is the classic SuperSocket Info: Bind failed on TCP port 1433 error in there.I also notice it says SQL server listening on Shared Memory, Named Pipes. and on the known good server it says SQL server listening on TCP, Shared Memory, Named Pipes.In both server and client TCP is enabled though. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-21 : 17:39:06
|
Then you have to let your windows admin to find out if the server set tcp/ip properly. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-21 : 17:55:34
|
Have you tried rebooting this problematic server? If you have rebooted it and still this TCP/IP error in the Error Log, then have you tried a different port? Once you select a different port, you must restart the service for it to take effect.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
ccyre
Starting Member
15 Posts |
Posted - 2008-02-21 : 18:14:15
|
Yes I have tried different ports and stopped the sql service and restarted. I have been tempted to use a port the server was listening to but then there would probably be application conflicts. Rmiao, I am the windows admin, but I am not an SQL admin :P Small bank and we wear multiple hats. I've only ever worked at companies with segregated teams (desktop, server, citrix, network, sql Teams) but here we are all of them at once. So what should I look for under the properties of the network connection under the properties of tcp/ip? I suppose I can compare with the known good server? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-21 : 22:07:37
|
What's ip address of virtual sql on the cluster? Can you ping that address? Anyone changed that address recently? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-21 : 22:08:59
|
By the way, data source in connection string should be virtual sql name in the cluster. |
|
|
ccyre
Starting Member
15 Posts |
Posted - 2008-02-21 : 22:25:41
|
10.1.172.7 is the ip of the virtual sql and yes we can ping it. The ip's of both servers were changed at one point so I had to change the virtual to follow suit. But I eventually had to uninstall SQL and reinstall on both because I never set it up as a cluster install initially. So that shouldn't be an issue.Can you explain your last post more rmiao? Where do I check to make sure the data source in the connection string is the virtual sql name. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-21 : 22:33:25
|
In your original post, you have Data Source=machineName. MachineName should be virtual sql instance name or its ip address. By the way, can you connect to sql with named pipes? Also check virtual sql ip address in registry -> hklm\software\microsoft\microsoft sql server\cluster, ensure it's 10.1.172.7 as well. |
|
|
ccyre
Starting Member
15 Posts |
Posted - 2008-02-22 : 06:36:29
|
The original post included a connection string someone on another post referenced, I'm not sure if it's going to help my situation. I'm not even sure where to look for a connection string. Inside an ini file, in the registry, etc etc.? Could you give me an example of how to connect with a named pipe (command line syntax)? Also I will check the registry and get back to you. Headed to work in an hour. |
|
|
ccyre
Starting Member
15 Posts |
Posted - 2008-02-22 : 07:54:54
|
hmmmm that registry entry shows the OLD ip address. Changing it now then restarting the SQL service/database. |
|
|
ccyre
Starting Member
15 Posts |
Posted - 2008-02-22 : 07:58:29
|
OMG it worked! Thanks huge! Now we will go back to what we were trying to do with installing an app and configuring it, hopefully it will not say invalid database log when clearly it was. I'll let you know how it goes. |
|
|
ccyre
Starting Member
15 Posts |
Posted - 2008-02-25 : 08:25:53
|
app works like a champ too. thanks again guys. |
|
|
Next Page
|