SQL server 2008 hosted by Windows 7 Pro(32bit)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied
I can not connect to the SQL from the Other PC which is on the same network (non-domain environment). I will explain the old setup and new setup.
OLD Setup
Main PC was XP Pro which was hosting Sql server and the custom made application which had the database. NOW the other computer on the network with Vista Business was accessing that program without any problem.
NEW Setup
NOW the XP PC died. So we replaced with new Windows7 Pro(32bit) PC.
The Main program which uses the SQL on the main pc is working fine and all the sql services are running.
The folder of the program been shared and the other PC can see it and access it.
SQL browser, SQL server program has been allowed in exceptions.
TCP protocol is enabled, Remote Connections is enabled in Database Properties.
TCP port: 1433 and UDP port: 1434 is allowed in the exceptions.
Troubleshooting done.
From the other PC when I telnet port 1433 then it was not connecting. And so I found out that the SQL hosting pc was giving random port. So I have given default port 1433 in the SQL. And after that I am able to telnet pcname 1433.
BUT I can not telnet pcname 1434. It keeps denying.
What else I can check to troubleshoot that why UDP port 1434 is not accepting the connection.
I was reading some article allowing port number in the string. But how can I do that if i got the string like Target path "\\pcname\programfolder\fi
Could it be the router. But the whole setup has been working fine before with XP Pro as the main pc.
any help will be appreciated
thanks
I can not connect to the SQL from the Other PC which is on the same network (non-domain environment). I will explain the old setup and new setup.
OLD Setup
Main PC was XP Pro which was hosting Sql server and the custom made application which had the database. NOW the other computer on the network with Vista Business was accessing that program without any problem.
NEW Setup
NOW the XP PC died. So we replaced with new Windows7 Pro(32bit) PC.
The Main program which uses the SQL on the main pc is working fine and all the sql services are running.
The folder of the program been shared and the other PC can see it and access it.
SQL browser, SQL server program has been allowed in exceptions.
TCP protocol is enabled, Remote Connections is enabled in Database Properties.
TCP port: 1433 and UDP port: 1434 is allowed in the exceptions.
Troubleshooting done.
From the other PC when I telnet port 1433 then it was not connecting. And so I found out that the SQL hosting pc was giving random port. So I have given default port 1433 in the SQL. And after that I am able to telnet pcname 1433.
BUT I can not telnet pcname 1434. It keeps denying.
What else I can check to troubleshoot that why UDP port 1434 is not accepting the connection.
I was reading some article allowing port number in the string. But how can I do that if i got the string like Target path "\\pcname\programfolder\fi
Could it be the router. But the whole setup has been working fine before with XP Pro as the main pc.
any help will be appreciated
thanks
ASKER
I have run the script and checked that all the ports are open and listening. But still no luck. When I went to the Properties of TCP\IP under Protocols for SQL Express, the Enabled were selected No and I changed it to Yes. Its been changed on IPv2, v4, v6. Then restarted the SQL Server and SQL Browser BUT still getting the same error.
ASKER
When I was going through the Windows Event Manager there was an error with Event ID 14 - The SQL Browser processing of requests against a particular IP address has encountered a critical error. Processing of requests from this address has been halted. Event ID 8 - The SQLBrowser service unable to process a client request. These errors comes when I tried to login from Vista PC to the Windows7 where SQL is installed and running.
I hope it gives bit more idea to understand the problem.
I hope it gives bit more idea to understand the problem.
Sounds to me that you are hitting a bug and need to service pack your SQLExpress instance.
What is your output for
SELECT @@VERSION
when you are in the SQL server from the Windows 7 pc?
This bug sounds like what you are hitting: http://support.microsoft.com/kb/2526552
You should make sure that you are patched to the latest service pack of your version of SQLExpress. The @@VERSION will tell us which version you are running.
What is your output for
SELECT @@VERSION
when you are in the SQL server from the Windows 7 pc?
This bug sounds like what you are hitting: http://support.microsoft.com/kb/2526552
You should make sure that you are patched to the latest service pack of your version of SQLExpress. The @@VERSION will tell us which version you are running.
ASKER
It is having Service Pack Level 3
Version Number: 10.3.5500.0
This is the last step left to be done. I am going to disable IPV6 as can not add connection string because of third-party application.
Is there any tool that can identify whether its the SQL Server authentication from client which is giving an issue?
cheers,
Version Number: 10.3.5500.0
This is the last step left to be done. I am going to disable IPV6 as can not add connection string because of third-party application.
Is there any tool that can identify whether its the SQL Server authentication from client which is giving an issue?
cheers,
ASKER
Quick question
the User Name on the Windows 7 PC where the SQL Server installed is "user" WHILE the User Name on the Network PC is "oem". Could that be a problem in authentication.
As when I was running SQLCMD -S computername -U username -P password it comes back with the error Msg 18456, Level 14, State 1, Server PCNAME\INSTANCENAME, Line 1, Login failed for user"username"
And when I run the same command with OEM user it throws the same error. What does it mean?
the User Name on the Windows 7 PC where the SQL Server installed is "user" WHILE the User Name on the Network PC is "oem". Could that be a problem in authentication.
As when I was running SQLCMD -S computername -U username -P password it comes back with the error Msg 18456, Level 14, State 1, Server PCNAME\INSTANCENAME, Line 1, Login failed for user"username"
And when I run the same command with OEM user it throws the same error. What does it mean?
If the account youre trying to use is a windows account, if so the SQLCMD should be used with the switch -E
Not -U and -P
Logon eith the windows account. If its not a domain you can create the same account and the same pwd on the different workstations. Log on with that account and use SQLCMD with the -E switch.
Regards Marten
Not -U and -P
Logon eith the windows account. If its not a domain you can create the same account and the same pwd on the different workstations. Log on with that account and use SQLCMD with the -E switch.
Regards Marten
ASKER
I was able to connect from the Vista computer using SQLCMD -Scomputername,tcp_port -E and it works whether from same user credentials or different on windows(non-domain) environment.
Now the main error remains ie DBNETLIB...Can anyone help me getting in the right direction to find the problem.
Cheers Shree
Now the main error remains ie DBNETLIB...Can anyone help me getting in the right direction to find the problem.
Cheers Shree
How does your connectionstring look like. How id it configured? What makes you determine it's the dbnetlib?
Regards Marten
Regards Marten
ASKER
The Third-party program needs to run over the network and its sitting on Windows 7 where the sQL is. And the Shortcut from the Shared folder is put it on the Desktop of Vista. So when we run that Shortcut of EXE it throws the error of DBNETLIB.
Now the Properties of the Shortcut is like
Target: \\computername\sharedfolde
Start-in: \\computername\sharedfolde
Now the Properties of the Shortcut is like
Target: \\computername\sharedfolde
Start-in: \\computername\sharedfolde
ASKER
I have also tried disabling IPV6 protocol from Windows7 and Vista but same error.
So my feeling goes to whether I have to supply the port number to the connection string.
The another thing to note down is When I rung PortQuery command for UDP 1434 it brings the message of FILTERED. Hope it helps.
In the windows event on SQL computer it shows me the Event ID 8 & 14 for SQL Browser service.
I am quite new to the sQL as well.
So my feeling goes to whether I have to supply the port number to the connection string.
The another thing to note down is When I rung PortQuery command for UDP 1434 it brings the message of FILTERED. Hope it helps.
In the windows event on SQL computer it shows me the Event ID 8 & 14 for SQL Browser service.
I am quite new to the sQL as well.
Make sure you have enabled Mixed mode authentication. It is off by default in SQL express. Right click on the server and choose properties and look in the security.
it appears that windows with works.
it appears that windows with works.
ASKER
It had been changed from Windows to Mixed Mode and then put it back to Windows. As it did not resolve.
ASKER
Just a comment to add - the Properties of Database in SQL Server Management Studio and then the Permissions. The user credentials is of still referring to the OLD PC. Will it make any difference.
I had a talk with the third-party developer and they said it will not affect as far as its Windows Authentication.
I had a talk with the third-party developer and they said it will not affect as far as its Windows Authentication.
If you execute Select @@SERVERNAME
does it show the current server name or the old one?
does it show the current server name or the old one?
ASKER
it shows the current.
Have you tried running the program in elevated mode?
Regards Marten
Regards Marten
The bottom line for you is that you need to ensure that the following are in place for connectivity.
TCP Enabled and 1433 is the Static Port - sounds like it is done.
SQL Server Port TCP 1433 and UDP 1434 should be allowed in the firewall
Windows Authentication user used with -E on SQLCMD
If you are going to use -U and -P you need to have Mixed authentication and then it will be a username and password set up in SQL Server, not in Windows
If you have problems, you can disable the firewall on the computer with SQL Server and see if it is resolved, and then you will know that it is connectivity, not configuration of SQLServer.
Ensure that you have Remote Connections enabled in sp_configure
When all these are in place and tested, you should be able to connect, and I think that you indicated that you were able to connect using SQLCMD -E, so I am not sure what is left.
TCP Enabled and 1433 is the Static Port - sounds like it is done.
SQL Server Port TCP 1433 and UDP 1434 should be allowed in the firewall
Windows Authentication user used with -E on SQLCMD
If you are going to use -U and -P you need to have Mixed authentication and then it will be a username and password set up in SQL Server, not in Windows
If you have problems, you can disable the firewall on the computer with SQL Server and see if it is resolved, and then you will know that it is connectivity, not configuration of SQLServer.
Ensure that you have Remote Connections enabled in sp_configure
When all these are in place and tested, you should be able to connect, and I think that you indicated that you were able to connect using SQLCMD -E, so I am not sure what is left.
ASKER
Hi Marten,
What is elevated mode?
What is elevated mode?
ASKER
Hi Genius,
Could it be Router? - But in the SQL server logs I got the Event ID 8 & 14. So it's not the router. Am I right?
Could it be the third-party database authentication that throws the error? Does SQL needs the permission from the database to process the query for Remote PC? But if that is the case then the Local PC where the SQL is installed runs fine?
thanks for the reply
Could it be Router? - But in the SQL server logs I got the Event ID 8 & 14. So it's not the router. Am I right?
Could it be the third-party database authentication that throws the error? Does SQL needs the permission from the database to process the query for Remote PC? But if that is the case then the Local PC where the SQL is installed runs fine?
thanks for the reply
ASKER
When I run the PortQuery command for UDP 1434on Remote PC or on the MainPC. The message remains same ie FILTERED. Hope it helps.
This thread is a mess, I think I read it all and understood the problem. Lets see
Everyone wants to help you fix the SQL Browser service. Lets do it professionally instead. You don't need the SQL Browser. Simply use the port in your connections.
So to simplify. open up the errorlog, and verify the port the instance is listening to. Should be 1433 in your case, though it's a named instance as I understand it.
Now configure your application to use this IP/Name,port and you should be fine.
I E a ODBC link for example:
If you write:
MyServerName\InstanceName then 1434 UDP is needed, lets assume the instance answers to port 1646. Now instead write:
MyServerName,1646 Now UDP 1434 is excluded, there is no need for a port lookup since it's been defined.
Is it possible to configure your application to use a defined port. I E in your case
YourServerNameOrIPIfPreffe
Then UDP is not needed at all. You could disable the SQL Browser service, and it will work anyway.
The 1433 is the defaultport, but for a ODBC, and JDBC also bye the way, you still need to specify the ,1433 portion. This has to do with the instance presenting itself as a named instance. Only the default instance (not named) does not need the ,1433 portion in the configuration. But it will not hurt. So as a rule of thumb ALWAYS include the port number.
Try, and please respond here.
Regards Marten
Everyone wants to help you fix the SQL Browser service. Lets do it professionally instead. You don't need the SQL Browser. Simply use the port in your connections.
So to simplify. open up the errorlog, and verify the port the instance is listening to. Should be 1433 in your case, though it's a named instance as I understand it.
Now configure your application to use this IP/Name,port and you should be fine.
I E a ODBC link for example:
If you write:
MyServerName\InstanceName then 1434 UDP is needed, lets assume the instance answers to port 1646. Now instead write:
MyServerName,1646 Now UDP 1434 is excluded, there is no need for a port lookup since it's been defined.
Is it possible to configure your application to use a defined port. I E in your case
YourServerNameOrIPIfPreffe
Then UDP is not needed at all. You could disable the SQL Browser service, and it will work anyway.
The 1433 is the defaultport, but for a ODBC, and JDBC also bye the way, you still need to specify the ,1433 portion. This has to do with the instance presenting itself as a named instance. Only the default instance (not named) does not need the ,1433 portion in the configuration. But it will not hurt. So as a rule of thumb ALWAYS include the port number.
Try, and please respond here.
Regards Marten
ASKER
Hi Marten,
thank you for taking it to a level when it can be understood.
You are right here BUT we can not supply the port number in this application here.
This is how the third-part application and SQL are connecting.
The Third-party program needs to run over the network and its sitting on Windows 7 where the sQL is. And the Shortcut from the Shared folder is put it on the Desktop of Vista. So when we run that Shortcut of EXE it throws the error of DBNETLIB.
Now the Properties of the Shortcut is like
Target: \\computername\sharedfolde
Start-in: \\computername\sharedfolde
waiting for your reply
thank you for taking it to a level when it can be understood.
You are right here BUT we can not supply the port number in this application here.
This is how the third-part application and SQL are connecting.
The Third-party program needs to run over the network and its sitting on Windows 7 where the sQL is. And the Shortcut from the Shared folder is put it on the Desktop of Vista. So when we run that Shortcut of EXE it throws the error of DBNETLIB.
Now the Properties of the Shortcut is like
Target: \\computername\sharedfolde
Start-in: \\computername\sharedfolde
waiting for your reply
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Marten,
When I use the application locally it works fine.
The UDL file is attached here for your reference.
Thanks Shree
When I use the application locally it works fine.
The UDL file is attached here for your reference.
Thanks Shree
Locally, not on the SQL server, but from another client?
ASKER
Sorry If I am confusing you.
We are not trying to use the SQL Remotely but its from LAN from another PC.
Like there is a Windows 7 PC where the SQL is hosted with the third-party application. And then there is a Vista PC which tries to open the file and brings the DBNETLIB error.
So these both PC are on LAN (non-domain)
I hope this is what you were asking.
Cheers Shree
We are not trying to use the SQL Remotely but its from LAN from another PC.
Like there is a Windows 7 PC where the SQL is hosted with the third-party application. And then there is a Vista PC which tries to open the file and brings the DBNETLIB error.
So these both PC are on LAN (non-domain)
I hope this is what you were asking.
Cheers Shree
ASKER
Hi Marten
Just an update. After you mentioned about the UDL I have put the Port 1433 in the string ie Select or Enter a Server name = \\mainpc\sqlexpress,1433. Then I the Test Connection and it succeeded.
Its working on the PC where the SQL is running.
NOW when I run from the trouble computer I get different error.
-- Cannot connect to database
Cannot open database "RentwareDB" requested by login. The login failed.
Just an update. After you mentioned about the UDL I have put the Port 1433 in the string ie Select or Enter a Server name = \\mainpc\sqlexpress,1433. Then I the Test Connection and it succeeded.
Its working on the PC where the SQL is running.
NOW when I run from the trouble computer I get different error.
-- Cannot connect to database
Cannot open database "RentwareDB" requested by login. The login failed.
ASKER
Hi Marten,
GOOD News.
And its working now.
Thanks for leading me to the right direction and taking the lead in helping me resolving the issue.
Keep up the good work mate.
Regards,
Shree
GOOD News.
And its working now.
Thanks for leading me to the right direction and taking the lead in helping me resolving the issue.
Keep up the good work mate.
Regards,
Shree
Youre welcome
ASKER
It was an excellent support as it was understood from the start. The advice has given thoughtfully and was not in general.
It was worth joining the Experts-Exchange.
It was the last day of my try with EE and I got the support I needed and that made to stay.
It was worth joining the Experts-Exchange.
It was the last day of my try with EE and I got the support I needed and that made to stay.
Kindly follow the steps here:
http://www.igorshapiro.com/2009/10/sql-server-2008-on-windows-7-enabling.html
Also make sure you restart SQL Server services once after the above changes are done and verify once..