Solved

SQL server 2008 hosted by Windows 7 Pro(32bit)

Posted on 2013-02-01
33
813 Views
Last Modified: 2013-02-05
[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\filename.exe \\pcname\programfolder\filename.udl". Start In "\\pcname\programfolder\filename.exe"

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
0
Comment
Question by:samsolutions
  • 19
  • 7
  • 4
  • +1
33 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 38842985
Seems like you missed something while doing any of the above..
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..
0
 

Author Comment

by:samsolutions
ID: 38845357
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.
0
 

Author Comment

by:samsolutions
ID: 38845371
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.
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38847794
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.
0
 

Author Comment

by:samsolutions
ID: 38847876
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,
0
 

Author Comment

by:samsolutions
ID: 38848118
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?
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38848136
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
0
 

Author Comment

by:samsolutions
ID: 38848210
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
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38848517
How does your connectionstring look like. How id it configured? What makes you determine it's the dbnetlib?
Regards Marten
0
 

Author Comment

by:samsolutions
ID: 38849567
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\sharedfoldername\filename.exe \\computername\sharedfoldername\filename.udl

Start-in: \\computername\sharedfoldername
0
 

Author Comment

by:samsolutions
ID: 38849572
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.
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38849592
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.
0
 

Author Comment

by:samsolutions
ID: 38849608
It had been changed from Windows to Mixed Mode and then put it back to Windows. As it did not resolve.
0
 

Author Comment

by:samsolutions
ID: 38849634
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.
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38849678
If you execute Select @@SERVERNAME

does it show the current server name or the old one?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:samsolutions
ID: 38849687
it shows the current.
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38850048
Have you tried running the program in elevated mode?

Regards Marten
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38852177
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.
0
 

Author Comment

by:samsolutions
ID: 38852710
Hi Marten,

What is elevated mode?
0
 

Author Comment

by:samsolutions
ID: 38852735
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
0
 

Author Comment

by:samsolutions
ID: 38854037
When I run the PortQuery command for UDP 1434on Remote PC or on the MainPC. The message remains same ie FILTERED. Hope it helps.
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38854241
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
YourServerNameOrIPIfPreffered,1433
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
0
 

Author Comment

by:samsolutions
ID: 38854370
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\sharedfoldername\filename.exe \\computername\sharedfoldername\filename.udl

Start-in: \\computername\sharedfoldername

waiting for your reply
0
 
LVL 20

Accepted Solution

by:
Marten Rune earned 500 total points
ID: 38854432
If you copy the files locally do they work.

Can you open the .UDL file in a notepad, and show me the code

Regards Marten
0
 

Author Comment

by:samsolutions
ID: 38854455
Hi Marten,

When I use the application locally it works fine.

The UDL file is attached here for your reference.

Thanks Shree
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38854468
Locally, not on the SQL server, but from another client?
0
 

Author Comment

by:samsolutions
ID: 38854489
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
0
 

Author Comment

by:samsolutions
ID: 38854500
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.
0
 

Author Comment

by:samsolutions
ID: 38854551
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
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38854595
Youre welcome
0
 

Author Closing Comment

by:samsolutions
ID: 38854604
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.
0

Featured Post

Integrate social media with email signatures

Is your company active on social media? Do you also use email signatures? Including social media icons in your email signature is a great way to get fans for free. Let all your email users know you’re on social media quickly and easily, in a single click.

Join & Write a Comment

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now