db connection problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - SQL Server 2005/Yukon

db connection problems

Author

Topic

deulu
Starting Member

6 Posts

Posted - 2006-09-25 : 05:36:08

Hi
I have this major problem:

I used to use sql 7.0 with vb6 thru this line of code connection:
.ConnectionString = "Data Source=" & SQL_SRV & ";User ID=sa" & ";Initial Catalog= NorthWind" //no password

Through an executable file, i was able to run my program from different stations (LAN network; database sql at my station)

Lately, i shifted from 7.0 to sql 2005.
With the following line of code connection:
.ConnectionString = "Data Source=" & SQL_SRV & ";User ID=sa" & ";Password=****" & ";Initial Catalog= NorthWind"

or through windows authentication:
.ConnectionString = "Data Source=" & SQL_SRV & "; Initial Catalog=NorthWind; Trusted_Connection=yes"

if i run my executable file on my station, it works perfectly fine.
But when i try to run the executable file from any other station, the following occurs:

run-time error '-2147467259 (80004005)':

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Any clue where could be the problem?

Thanks

Rishi Maini SQL2K5 Admin
Yak Posting Veteran

80 Posts

Posted - 2006-09-25 : 06:04:11

Your SQL 2005 Server may not be configured to accept remote connections. You need to go to under "All Programs--> Microsoft SQL Server 2005--> Configuration Tools--> Surface Area Configuration" and select "Surface Area configuration for Services and connections" and enable "Local and Remote connections".

Restart SQL Services and you should be good to go

Thanks
Rishi Maini

deulu
Starting Member

6 Posts

Posted - 2006-09-25 : 07:35:17

Thanks Rishi

but it's already enabled..

This is so frustrating..any other possible solution?

mr_mist
Grunnio

1870 Posts

Posted - 2006-09-25 : 08:02:55

Northwind doesn't normally exist in 2005. You may be getting an error because it's trying to connect to that.

Also make sure you are in mixed auth mode to connect as SA. Not that I'd recommend doing that anyway.

-------
Moo. :)

Rishi Maini SQL2K5 Admin
Yak Posting Veteran

80 Posts

Posted - 2006-09-25 : 08:21:39

When you said that :-

"if i run my executable file on my station, it works perfectly fine.
But when i try to run the executable file from any other station, the following occurs:"

This lets me believe that the connection string at your end is fine and its just that the remote clients are unable to connect to this SQL 2005 instance.

1. Can you check if you can create a ODBC connection from the remote Clients to this Server ?
2. Please also check that the "SQL Server Browser" service is in a start state on the SQL Box.
3. Check in SQL Error Logs if SQL is listening on all the protocols and check the port number also on which its listening.
By Default the location of the error logs are at (c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\Errorlog) . Open this "ErrorLog" file in Notepad and check the protocol status.
4. Try to create either a Named Pipe or TCP/Ip Alias on the remote Clients if it works.

I think starting SQL Browser Service on the SQL Box should work , if it does not let us know.

Thanks
Rishi Maini

deulu
Starting Member

6 Posts

Posted - 2006-09-25 : 09:01:49

quote:
Northwind doesn't normally exist in 2005. You may be getting an error because it's trying to connect to that.

Northwind was just an example...

As for the steps you mentioned rishi,
here:
1. I'm already running odbc and it's also functioning well ...and as i said before, sql 7.0 was running perfectly.

2.Sql server browser wasn't running but i did run it.. and i tried again but nope.. still nothing.

3. i have this error log file .. am not sure exactly what u meant by "listening on all the protocols" though i have login failure errors..
Error: 18456, Severity: 14, State: 16.
Login failed for user 'NT AUTHORITY\SYSTEM'
Error: 18456, Severity: 14, State: 8.

4.Using both TCP/IP and named pipes was checked but also nothing.

Any further clues???

Rishi Maini SQL2K5 Admin
Yak Posting Veteran

80 Posts

Posted - 2006-09-25 : 09:11:28

I am willing to help you out but I need some more info:-

A.) Paste first 30-35 lines of your latest SQL Error Logs + the exact connection string that you are using in your application.

Note:- Pasting the SQL Error Logs may reveal your ServerName, IP address and Port Number on which SQL is listening to the outer world. Is that fine with you ?

Thanks
Rishi Maini

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-09-25 : 13:26:26

remove named pipes and enable only TCP/IP

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-09-25 : 13:43:55

...Also,
I noticed the first time you were getting connection errors. But now you are getting login errors

Did you create the user and give him access to your db ?

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-09-26 : 01:48:36

Are you running WinXP SP2? Do you have Windows Firewall enabled (or any other personal firwall for that matter)? If so make an exeption for 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe' (or whereever your server installation ended up.

-- The Heisenberg uncertainty principle also applies when debugging

deulu
Starting Member

6 Posts

Posted - 2006-09-26 : 02:52:28

Hi rishi again,
am not sure about pasting the sql error logs here .. if i can post them privately to u..i don't mind..
is there a way?

and "AFRICA"
what do u mean by creating a user n give him an access to my db?..
how can i do that..i have no previous clue to this...

(So desperate now..pls help..cuz this is soo urgent and ppl want to move on working with the program

)

N.B. 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SqlWb.exe'
Also N.B.
1. the log error file is on my machine..
2.there is no sql 2005 installed on the other machines..but when i used to work with sql 7.0 it wasn't installed there too..
3. It is only an executable file, so it only has to connect to my pc to work fine, so all the machines can access my pc (my C drive is all shared).

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-09-26 : 03:19:07

The firwall on the machine that runs the server not the one that cant connect. SqlWb.exe is not the server but SSMS.

-- The Heisenberg uncertainty principle also applies when debugging

deulu
Starting Member

6 Posts

Posted - 2006-09-26 : 03:34:31

quote:
Originally posted by PSamsig

The firwall on the machine that runs the server not the one that cant connect. SqlWb.exe is not the server but SSMS.

-- The Heisenberg uncertainty principle also applies when debugging

Yeah, sorry..
i rechecked that, it was already in the firewall exceptions..
thanks

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-09-26 : 06:08:47

I havent used VB6 in years, so really rusty on that.

However, i know that if you are connecting a vb network app from a remote client to a server. Which seems to be your case, you should have MS SQL client installed on the remote station and connected to the server. Is this your case ???

To create a database user, in MSSQL 2k, You create it in security and give it permission to login to the respective database.

I have not done this in 2005

hope this helps

Afrika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources