Can't Connect to Remote Server Via Visual Basic 2008

This is a common question. So common, I wrote a whitepaper on how to address
the issues.
http://betav.com/blog/billva/2006/06/getting_and_staying_connected_1.html


--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Dewayne Balentine" wrote in
message
> Team -
>
> Totally lost here. New to Sql.
>
> I have an XP server running MsSql2005 Express just fine. (Server Name:
> BLSql1).
>
> Doing Development work on a workstation that needs to connect to server.
> No
> matter what I do I can't connect. Everything I have read says enable
> remote
> connections which I have and still no luck. I get the common error of:
>
> "An error has occurred while establishing a connection to the server.
> While
> connecting to SQL Server 2005, this failure may be caused by the fact that
> under default settings SQL Server does not allow remote connections.
> (provider: SQL Network Interfaces, error 26 - Error locating
> server/instance
> specified)"
>
> Within Visual Basic I can browse to the file I want to use but that's
> about
> it.
>
> Background Info (not sure if needed or not)
> 1. No sql management loaded on workstation.
> 2. Remote connections are enabled.
> 3. Not seeing an instance of the server, can only browse to file.
> 4. killed all firewall software for testing and still no luck.
>
> Please help a newbie out!
> Thank you - Dewayne
 >> Stay informed about: Can't Connect to Remote Server Via Visual Basic 2008 

Rick & William -

Thank you for your responses.

Rick:

1. Yes, server is actually an XP Pro Box.
2. Yes, SQL Browser Service is running (not sure where to add port number,
reviewing now)
3. Yes, I am connecting via a named instance as you have it.

VB2008 has setup most of the connection for me. What keeps catching my eye
is that the data source is .\SQLEXPRESS which just doesn't seem right.

William:

Currently re-reading your article. Very gun shy right now so having to read
every little line word by word.

My problem is definatly an issue between the server and the workstation. If
I us VB2008 on the server itself I connect to the file without a problem.

Just to verify:
1. I don't need any sql tools (sql manager) on the workstation.
2. IIS doesn't need to be running for this type of configuration, right?

Thanks for all your help.
Dewayne
 >> Stay informed about: Can't Connect to Remote Server Via Visual Basic 2008 

Ok, without the ENTIRE connection string we can't really tell much about
what you're doing. By default VS (2005 or 2008) uses User Instance
connections which are (IMHO) tougher to get working correctly. The
..\SQLEXPRESS data source is correct if you are trying to connect to the
SQLEXPRESS named instance of SQL Server on the local (not remote) system.
This approach is fine but you say you're referencing a "file"--I assume this
is the .mdb database file. I expect that your drag-and-drop connection is
using User Instance which makes a copy of the SQLEXPRESS instance in the
user area of the hard disk.
The "." as server name eliminates the network layer which should simplify
the operation. However, I recommend that you attach the .mdb file to the
local SQLEXPRESS instance. This is a one-time operation that dramatically
simplifies connecting. Make sure the .mdb file is not part of your project
but kept on a local drive (not a networked drive). I discuss this at length
in my 7th Edition (Chapter 9).


__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Dewayne Balentine" wrote in
message
> Rick & William -
>
> Thank you for your responses.
>
> Rick:
>
> 1. Yes, server is actually an XP Pro Box.
> 2. Yes, SQL Browser Service is running (not sure where to add port
> number,
> reviewing now)
> 3. Yes, I am connecting via a named instance as you have it.
>
> VB2008 has setup most of the connection for me. What keeps catching my
> eye
> is that the data source is .\SQLEXPRESS which just doesn't seem right.
>
> William:
>
> Currently re-reading your article. Very gun shy right now so having to
> read
> every little line word by word.
>
> My problem is definatly an issue between the server and the workstation.
> If
> I us VB2008 on the server itself I connect to the file without a problem.
>
> Just to verify:
> 1. I don't need any sql tools (sql manager) on the workstation.
> 2. IIS doesn't need to be running for this type of configuration, right?
>
> Thanks for all your help.
> Dewayne
>
>
 >> Stay informed about: Can't Connect to Remote Server Via Visual Basic 2008 

William -

Thank you so much for staying with me on this.

Here is the connection string that is being generated

Data
Source=.\SQLEXPRESS;AttachDbFilename=\\Blsql1\Data\TESTDATABASE.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

Remember, data is on a shared network drive. Anything jumping out as wrong?

Dewayne
 >> Stay informed about: Can't Connect to Remote Server Via Visual Basic 2008 

Bingo. You CAN'T have the .MDB on a shared network drive. SQL Server won't
permit it.
That's how JET/Access works, but SQL Server requires that the data be on the
local data bus so it can ensure that the data gets written.


--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Dewayne Balentine" wrote in
message
> William -
>
> Thank you so much for staying with me on this.
>
> Here is the connection string that is being generated
>
> Data
> Source=.\SQLEXPRESS;AttachDbFilename=\\Blsql1\Data\TESTDATABASE.mdf;Integrated
> Security=True;Connect Timeout=30;User Instance=True
>
> Remember, data is on a shared network drive. Anything jumping out as
> wrong?
>
> Dewayne
 >> Stay informed about: Can't Connect to Remote Server Via Visual Basic 2008 

William -

Thank you! No wonder I could connect locally but not via the network.

Now I am atleast headed in a different direction. Now I only have rethink
how to connect the different programs to the central database. Gotta love a
new challenge when starting with almost no knowledge!!

BTW, heading out to get your book. I am honored that you would take the
time to help the "little guys".

Thanks again!
Dewayne
 >> Stay informed about: Can't Connect to Remote Server Via Visual Basic 2008 

Okay, you need to think about how SQL Server is intended to be used--it's
"native" client/server mode.
In this architecture you host SQL Server on a dedicated box (does not have
to be a Core Duo Quad) that's visible to the LAN (rarely if ever to the
WAN). Clients connect and send TSQL queries and get resultsets. The database
file is attached to the SQL Server instance and left in place permanently.
All changes are recorded there and the transaction log is maintained there.
This makes for central backup and admin and opens up a wealth of features
that leads to a highly scalable system.
If you need WAN access, think about an ASP.NET or similar web-based
architecture. Again, SQL Server is hosted on a single system and the
database is accessed by the IIS-hosted code. This is (virtually) the only
safe way to access SQL Server over the web--unless you use a VPN.

I'm also available for onsite or telephone mentoring if you want to go over
your architecture. Give me a call if you want to discuss rates.
Incidentally, there are no "little" guys. There are simply people with
issues.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Dewayne Balentine" wrote in
message
> William -
>
> Thank you! No wonder I could connect locally but not via the network.
>
> Now I am atleast headed in a different direction. Now I only have rethink
> how to connect the different programs to the central database. Gotta love
> a
> new challenge when starting with almost no knowledge!!
>
> BTW, heading out to get your book. I am honored that you would take the
> time to help the "little guys".
>
> Thanks again!
> Dewayne
 >> Stay informed about: Can't Connect to Remote Server Via Visual Basic 2008