Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 24th, 2008, 05:46 PM
Authorized User
 
Join Date: Jul 2007
Location: Denver, CO, USA.
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default Remote access of a SQLEXPRESS database with VB2008

Hi,

I'm not sure how to phrase the question.... here is the situation.

I'm currently designing a client application that connects to a SQL Server 2005 database. My app is being used to display data, allows users to run reports and stats on the data. It is being developed using VB2008. Within my VB2008 project, the connection string to the DB is made via SQLEXPRESS.

The road block that I'm running into right now is that it only allows me to connect to a local DB file (.mdf) only. I tried to connect to a different DB file on a server within the same network domain, and it generated the following error:

"The file "U:\Data.mdf" is on a network path that is not supported for database files. An attempt to attach an auto-named database for file U:\Data.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share." (where U:\ is a mapped network drive).

Below is the actual connection string in my code:

"SERVER=.\SQLEXPRESS;AttachDbFilename=U:\Data.mdf; Integrated Security=True;Connect Timeout=30;User Instance=True"

The intention is to have the DB reside on a server, where there is another application that runs as a service to collect the data and then populates this DB. Then my app would just connect to the database, and allows users to run reports based on the data in the DB.

I heard that this is the limitation of SQLEXPRESS: it will not allow connection to a remote database. This is true? If so, is there anyway that I can get around this limitation?

The main reason that I chose SQLEXPRESS in the first place is that because of the licensing issue. The company that I work for do not want to make the users of our software app have to purchase additional license for the full-blown SQL Server Enterprise edition. I also heard that SQL Server Enterprise edition does allow you to connect to a remote DB (.mdf) file.

I'm quite new to the whole SQL Server 2005 and VB2008 development environment. I do appreciate any help on this matter.

Thank you very much for all your help.



Khoi Nguyen
__________________
Khoi Nguyen
Reply With Quote
  #2 (permalink)  
Old June 24th, 2008, 10:49 PM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Location: Wellington, FL , USA.
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Well, personally I never connect directly to an mdf file. I have written several applications against SQLExpress and each time I use the same type of connection and connection string:

"SERVER=.\SQLEXPRESS;Database=databasename;Integra ted Security=True;Connect Timeout=30;User Instance=True"

If you haven't yet, download SQL Server Express with Advanced Services. That comes with SQL Server Managment Studio and let's you work with SQL Server just like you would all the other versions.

I would recommend not working with an mdf directly. Work on the database level and you'll see a lot of your connections issues go away...

========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================
Reply With Quote
  #3 (permalink)  
Old June 25th, 2008, 10:05 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,413
Thanks: 0
Thanked 16 Times in 16 Posts
Default

The core problem relating to the error you are getting has to do with the location of the data file. Regardless of how you set up the database, either "mounted" (for lack of a better term) to the running instance of SQLExpress, or dynamically connected with the .MDF file in the connection string, you still can't use a database located on a network share, even if it's a mapped drive. SQL Server requires disk access that isn't supported over network shares. The file must be on a local drive.

I have been trying to connect to my instance of SqlExpress from another machine but I haven't been able to. However, for some reason I'm also unable to connect to my instance of Sql Server 2005 Developer Edition which I certainly *should* be able to do so I can't say whether you should or shouldn't be able to connect to a SQL Express instance over a network.

Despite that, some googling should get you much information on the topic of connectivity.

-Peter
compiledthoughts.com
Reply With Quote
  #4 (permalink)  
Old June 25th, 2008, 10:27 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,413
Thanks: 0
Thanked 16 Times in 16 Posts
Default

I did finally manage to get connected to my instances of SQL server, both the regular one and express. I was testing SQL connectivity using telnet (with port 1433). However, this doesn't seem to work when you ONLY have named instances. I'm not sure how this has anything to do with a simple TCP/IP connectivity test, but it seems to.

Anyway, you can connect to an instance of SQL Express, you just need to ensure that network protocols are enabled (i.e. TCP/IP) otherwise only the local machine will be able to connect, using shared memory.

-Peter
compiledthoughts.com
Reply With Quote
  #5 (permalink)  
Old June 25th, 2008, 12:41 PM
Authorized User
 
Join Date: Jul 2007
Location: Denver, CO, USA.
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your comments. I tried to connect to a SQLEXPRESS instance on another computer through SQL Server Managment Studio, and I could see the database. I went ahead and added and grant myself permission to view the database. Then went back into VB2008, put in the following connection string:

"SERVER=ITMAN\SQLEXPRESS;Database=AlarmConf;Integr ated Security=True;Connect Timeout=30;User Instance=True"

When I tried to open the connection using the above connection string, I got the following error:

"User does not have permission to perform this action."

This is really odd! I had already created a new user within SQL Management Studio, and gave myself access to the AlarmConf database. How come it still does not allow me to access it?

Any thoughts and ideas would be greatly appreciated.

Thank you.


Quote:
quote:Originally posted by SQLScott
 Well, personally I never connect directly to an mdf file. I have written several applications against SQLExpress and each time I use the same type of connection and connection string:

"SERVER=.\SQLEXPRESS;Database=databasename;Integra ted Security=True;Connect Timeout=30;User Instance=True"

If you haven't yet, download SQL Server Express with Advanced Services. That comes with SQL Server Managment Studio and let's you work with SQL Server just like you would all the other versions.

I would recommend not working with an mdf directly. Work on the database level and you'll see a lot of your connections issues go away...

========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================
Khoi Nguyen
Reply With Quote
  #6 (permalink)  
Old June 25th, 2008, 01:31 PM
Authorized User
 
Join Date: Jul 2007
Location: Denver, CO, USA.
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks, but could you please explain a little bit more details? I'm quite new to SQL Server 2005, and I'm not sure if I understand you totally yet.

Thanks.

Quote:
quote:Originally posted by planoie
 I did finally manage to get connected to my instances of SQL server, both the regular one and express.  I was testing SQL connectivity using telnet (with port 1433).  However, this doesn't seem to work when you ONLY have named instances.  I'm not sure how this has anything to do with a simple TCP/IP connectivity test, but it seems to.

Anyway, you can connect to an instance of SQL Express, you just need to ensure that network protocols are enabled (i.e. TCP/IP) otherwise only the local machine will be able to connect, using shared memory.

-Peter
compiledthoughts.com
Khoi Nguyen
Reply With Quote
  #7 (permalink)  
Old June 25th, 2008, 11:10 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,413
Thanks: 0
Thanked 16 Times in 16 Posts
Default

[Repost, sorry made a few mistakes.]

You connection string indicates that you are connecting as your windows user. You said that you "...already created a new user within SQL Management Studio...". This would imply that you wish to connect using SQL Server Authentication, so you need to provide the SQL login credentials in the connection string instead of relying upon your windows identity credentials.

Take a look here for many variations on connection strings:
http://www.connectionstrings.com

-Peter
compiledthoughts.com
Reply With Quote
  #8 (permalink)  
Old June 26th, 2008, 12:50 PM
Authorized User
 
Join Date: Jul 2007
Location: Denver, CO, USA.
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

I tried it again, and this time, provided the credentials that I created within Server Management Studio.  It works fine.  I could open a remote database.

Throughout this exercise, I have learned that you can only open a database that resides in a SQLEXPRESS instance on a different computer.  However, SQL Server will not allow you to attach a remote DB file.  Is this statement correct?

Because of this, there is one huge problem for us though.  How is it possible for us to distribute our software app., and just have the client install it on their server, and have the installer automatically register the database file with the currently running instance of SQLEXPRESS on that server?  I could be wrong, but so far, I have only found that the remote database that I want to connect to must be created from scratch from that instance of SQLEXPRESS.  Does this mean that I will need to have Server Management Studio generate scripts for me to create the DBs and tables within the DBs for me?  and then distribute these scripts along the the app install?

I'm so new at this that there is a lot of stuff regarding how to distribute an app written in VB2008 that I'm still not sure about.  Especially the database portion of the app.  How to distribute the database templates reliably and requiring minimal IT involvement on the part of our customers to set this up.

Thanks again for your help.


Quote:
quote:Originally posted by planoie
 [Repost, sorry made a few mistakes.]

You connection string indicates that you are connecting as your windows user.  You said that you "...already created a new user within SQL Management Studio...".  This would imply that you wish to connect using SQL Server Authentication, so you need to provide the SQL login credentials in the connection string instead of relying upon your windows identity credentials.

Take a look here for many variations on connection strings:
http://www.connectionstrings.com

-Peter
compiledthoughts.com
Khoi Nguyen
Reply With Quote
  #9 (permalink)  
Old June 26th, 2008, 02:44 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,413
Thanks: 0
Thanked 16 Times in 16 Posts
Default

I don't really have any experience with this kind of deployment. All my work has been with web apps that we host so we can do all the setup manually.

One option is to create a complete script that creates the database and all its objects. Another could be to deploy a prepared DB file set and then just a script that attaches it to the DB engine. Then you would only need a few bits of information, mainly the local (to the server) file path that the database will live. The user would need to provide you a username that has the rights to do either of these options.

Quote:
quote:
Throughout this exercise, I have learned that you can only open a database that resides in a SQLEXPRESS instance on a different computer. However, SQL Server will not allow you to attach a remote DB file. Is this statement correct?
I'm not sure you've stated this correctly. I was able to connect to my SQL Express instance from the local and remote machines.

You're correct on the second point. You can only attach a database that lives on the SQL Server's local drives. This is typically not a problem because the remote clients just connect to the SQL server over the network anyway. If you need/want to host a database file on a networked file system you need to look into a much more complicated and robust system. We use Veritas software to manage some SAN connected storage hosted on an EMC storage system. From the perspective of the actual Windows machine(s) running SQL Server, the disk space appears as local storage so it works.

-Peter
compiledthoughts.com
Reply With Quote
  #10 (permalink)  
Old June 26th, 2008, 06:33 PM
Authorized User
 
Join Date: Jul 2007
Location: Denver, CO, USA.
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Peter for all your help.

Quote:
quote:Originally posted by planoie
 I don't really have any experience with this kind of deployment.  All my work has been with web apps that we host so we can do all the setup manually.

One option is to create a complete script that creates the database and all its objects.  Another could be to deploy a prepared DB file set and then just a script that attaches it to the DB engine.  Then you would only need a few bits of information, mainly the local (to the server) file path that the database will live.  The user would need to provide you a username that has the rights to do either of these options.

Quote:
quote:
Throughout this exercise, I have learned that you can only open a database that resides in a SQLEXPRESS instance on a different computer. However, SQL Server will not allow you to attach a remote DB file.  Is this statement correct?
I'm not sure you've stated this correctly.  I was able to connect to my SQL Express instance from the local and remote machines.  

You're correct on the second point.  You can only attach a database that lives on the SQL Server's local drives.  This is typically not a problem because the remote clients just connect to the SQL server over the network anyway.  If you need/want to host a database file on a networked file system you need to look into a much more complicated and robust system.  We use Veritas software to manage some SAN connected storage hosted on an EMC storage system.  From the perspective of the actual Windows machine(s) running SQL Server, the disk space appears as local storage so it works.

-Peter
compiledthoughts.com
Khoi Nguyen
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
HOWTO VB2008: VB6 ActiveX /WebBrowser Philibuster Visual Basic 2008 Essentials 1 August 11th, 2008 10:59 PM
connecting to a remote Access Project Database bright_mulenga Access 3 August 23rd, 2006 07:18 AM
connecting to a remote Access Project Database bright_mulenga Access 2 August 15th, 2006 07:33 AM
Remote access to an Access Project Database bright_mulenga Access 0 February 9th, 2006 10:51 AM
how to connect to remote ms-access database Jitendra Pro VB Databases 1 September 20th, 2004 04:13 AM



All times are GMT -4. The time now is 12:25 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.