Remote SQL connection problem, though works fine with SSMS

timothyjb

Member
Joined
Aug 10, 2010
Messages
5
Location
Rochester Hills, MI
Programming Experience
10+
I can successfully make a connection to a database on a remote server using SQL Server Management Studio, but when I try to connect to the same database using the add connection wizard in Server Explorer in VB.NET 2008 (Standard version), I get the following error message:

The File "\\Server\sql data\databasename.mdf" is on a network path that is not supported for database files. An attempt to attach an auto-named database for file \\Server\sql data\databasename.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I can however connect to a system database on the remote server - "master", for instance. I just can't connect to a database in a directory on the server, even though I can connect using Management Studio.

I'm using SQL Server 2008 Workgroup. My operating system is Vista Home Premium. I intend to try the connection on a workstation using Vista Business as soon as I can get access to it.

Tim
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,469
Location
Norway
Programming Experience
10+
Are you really connecting to the file with SSMS, or to the remote db server? And are you intending to use local or remote Sql Server? If you are using local Sql Server and that is Express edition then it will not allow attaching to file on network shares, not sure if even full Sql Server allows that, but it would be very uncommon doing that anyway. If you are using remote Sql Server have a look in the 'add connection' dialog, there is a 'change data source' button where you can change from 'sql server db file' to just 'sql server' to have server options rather than file options for connectivity. In a network environment it is most common to have Sql Server in a central machine and clients connecting to that, rather than local db servers connecting to a remote file.
 

timothyjb

Member
Joined
Aug 10, 2010
Messages
5
Location
Rochester Hills, MI
Programming Experience
10+
I'm connecting remotely to a db server. In SSMS I start with the Connect To Server dialog box and choose the remote server and click "Connect". Then, from the left pane of SSMS I right click on the Databases folder, choose Attach..., then I browse for the .mdf file in a folder on the server hard drive. I can then inspect or modify the database tables from the left pane, no problems.

As for VS.NET, I start the Add Connection wizard, Microsoft SQL Server (SqlClient) is already selected (NOT Microsoft SQL Server Database File (SqlClient)), I select the remote server name, which does appear in the list, fill in user name and password for sql server authentication, select Attach a Database File, then browse for the db file on the remote server. When I test the connection, that's when I get the error. Something new I just tried is doing this from a workstation that has Vista Business instead of Home Premium. Everything is the same except for when I get to the point where I browse for the db file. When I try to select the database file I get the error message "You don't have permission to open this file. Contact the file owner or an administrator to obtain permission." And I don't even get the chance to test the connection.
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,469
Location
Norway
Programming Experience
10+
In 'Attach a Database File' you probably have to specify a path that is local from server point of view. You can also attach the db in server and leave it like that, then in connection specify server/db.
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,469
Location
Norway
Programming Experience
10+
Using SSMS, once the db is attached to the Sql Server service it stays there until detached. That is regular operations with Sql servers. Last time I did this was four years ago, and that db is still in use in a multiuser network environment.
Dynamically attaching a new db file with connectionstring is something that is normally only done in local environment with single user dbs.
 

timothyjb

Member
Joined
Aug 10, 2010
Messages
5
Location
Rochester Hills, MI
Programming Experience
10+
John,

That solved it! I attached the db using SSMS at the remote server, shut down SSMS, and then at the other workstation I used the Add Connection wizard and this time the db showed up in the combo list under the Select or enter a database option. The connection was successful and I'm able to modify the database in Server Explorer.

Thanks a million.

Tim
 
Top Bottom