Avatar of Joe Kaminski
Joe Kaminski
Flag for United States of America asked on

Opening SQL Server 2008 Remotely

I have a Windows Server 2008 R2 network.  On the server resides a vb.net executable application program and a SQL Server 2008 database.  When I am logged directly onto the server I can launch the executable and open the database.  Unfortuantely, Windows Server 2008 does not support Windows 7 style graphics (i.e., forms with rounded corners and banners with opacity).  However, when I navigate from my desktop to the folder on the server with the executable and launch the executable I do get the Windows 7 style graphics, but I get the following error when I try to open the database with these statements.

        SqlConnection0.ConnectionString = "Data Source=" + strDataServer + ";Integrated Security=True;Initial Catalog=" + strDataBase
            SqlConnection0.Open()

strDataServer = "BDP-Server/SQLEXPRESS
strDataBase    = "BDP_Systems"

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)

SQL is configured with the "Allow Remote Connections" box checked.

Any thoughts?

Thanks.
RoutersMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Joe Kaminski

8/22/2022 - Mon
DrewKjell

Are you able to connect to the database using Management Studio from your workstation?
ASKER CERTIFIED SOLUTION
CmdoProg2

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Joe Kaminski

ASKER
DrewKjell,
I can not connect from my workstation.  The network or any of the mapped drives does not appear in SQL Server Management Studio when I try to attach a database.  I only see my local drives.

CmdoProg2,
I can ping BDP-Server.  I found a Microsoft article that describes how to open the SQL Server port.  It has a "Microsoft Fix-It" link to set up the port.  Should I use this?  I haven't used one of these "Fix-It" links before and I want to be careful with our server.

Also, will I have to install SQL Server on every workstation that will access the Windows Sever 2008 application programs or will the Windows Server 2008 SQL Server provide the access?  I'm not sure I want to load SQL Server onto every workstation (around 20 of them).

Thanks.
CmdoProg2

The default port is 1433 and is set on BDP-Server's firewall.  I haven't use a "Microsoft Fix-it" link either.  On the reference page, it has the procedures on how to open the port on the server.  Depending on your network configuration, there may be additional firewalls (I'm not a firewall expert, but are very familiar with network architexture).  May want to ask on how to limit IP addressed to the port.

The Server, BDP-Server, provides the engine (access) and SQL Server is not needed on the workstations.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Joe Kaminski

ASKER
CmdoProg2,
I tried to use the "Microsoft Fix-It" and it said that the "Fix-It" wasn't for my version of the operating system, I'm guessing it's Windows Server 2008 v. Windows Server 2008 R2, but again, just a guess.  So I went and temporarily turned off the firewall and tried to access the database.  I got the same error message.  Am I missing something?  Thanks.
CmdoProg2

Let me check on some thing... meanwhile I would put your firewall on the server back on.
CmdoProg2

try specifying the port on the connection string with port 1433 open on the server:


strDataServer = "BDP-Server/SQLEXPRESS,1433"

also check this thread on a similiar issue
http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/acf1d8c7-199c-46bc-a84c-c4778e72d883/
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Joe Kaminski

ASKER
CmdoProg2,
We're close!  With the SQL Server Configuration Utility I enabled TCP/IP and set the IP Address to the IP Address of the server.  When I turn off the firewall everything works.  So the question is, how do I get Port 1433 (the BDP-Server/SQLEXPRESS,1433 did not work, I got an error during program execution) configured for SQL Server?  What do you think?
CmdoProg2

Oops.... drop the port specification, only one of my instances use the IP address with the port specification; otherwise it throw an error.
strDataServer = "BDP-Server/SQLEXPRESS"

 SQL uses 1433 and 1434 ports.  1434 is for SQL Browser.  I would open ports as needed until
1.  Open 1433 and test with the new connection string
2.  Open 1434
3.  Turn off the firewall


Joe Kaminski

ASKER
On the Windows 2008 server I used the "Windows Firewall w/ Advanced Security".  I added new Inbound rules to allow Ports 1433 and 1434 and any program.  Still no luck.  Also, we have Symantec BackupExec on our server and it looks like there are already firewall rules to allow SQL Server and SQL Browser through the firewall.  I feel like we're right there.  Anything else?  Thanks.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
CmdoProg2

Did you drop the port specification on the connection string? I pretty sure you did, but it is the only thing right now I can think is missing...
Joe Kaminski

ASKER
I did drop the port specification.  I'm heading out of the office shortly.  Let me know if you think of anything else and I'll check back in.  Thanks.  Have a nice weekend.
SeanDevoy

I notice that your connection string includes "/SQLEXPRESS".  That is of course the default instance name of a Sql Server Express instance.  The 2 main differences in SQL Server Express are: NO BACKUP capability and absolutely no network support.  You CANNOT connect to an EXPRESS instance from another system.

Sorry, but I hope that helps.

Don't forget to check the right answer.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Éric Moreau

check "To add a program exception to the firewall using the Windows Firewall item in Control Panel." from http://msdn.microsoft.com/en-us/library/cc646023.aspx
Joe Kaminski

ASKER
SeanDevoy,
I understand what you're saying about SQLEXPRESS and networking.  I think I may have regular SQL Server on our Windows Server 2008 machine.  I'm not sure how to determine this.  However, when I turn off the Windows Firewall my VB program can access the SQL database from a desktop computer.  Doesn't this say that I have network support?  Let me know.  Thanks.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Joe Kaminski

ASKER
Thanks CmdoProg2 and emoreau.
emoreau, I did check the link.  I was getting "thrown off" because sqlserver.exe was already in the list of applications, however, it was in the list pointing at the BackupExec folder.  When I added sqlserver.exe to the list from the "standard" SQL Server folder everything started to work.  Thanks again.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23