How to enable clients to access the same DB via SQL Express

Hi to all!

My problem comes from this situation: I have two PC connected via a network calble; on the first I have SQL Server 2005 Express, an MDF data file in a shared directory under the "Document and Settings" path, and my program written in VB .NET 2005. On the second PC I have only my program, and I would like to have it connect to and work on the MDF on the first PC.

The program on the first PC works well and uses a connection string like the following:

Data Source=.\SQLEXPRESS;AttachDbFilename=<path>\<name>.mdf;;Integrated Security=True;User Instance=True

I am able to access to SQL Express from the second PC (I have enabled the Client to use the right TCP ports, told the firewall to let them work and enabled the Guest user on the first PC to use SQL Express), but from Visual Studio Server Explorer I cannot reach my data file: any attempt fails telling me that I cannot open it because is in a shared location... I can only open the DBs located in the "Data Directory" of SQL Express...

In other words, what should I do to have two or more clients work on the same DB under SQL Express? Is it mandatory that the DB .MDF file resides in the "C:\Programs\Microsoft SQL Server\MSSQL.1\MSSQL\Data" directory so that it can be permanently attached to SQL Express?

Thanks for your help!!!

Roberto

Permanently attaching the DB would be the easiest way to go. You do not have to have the mdf in SQL server data directory. You can put it on any local drive and attach it from there.

As connection string use Data Source=yourDBServer\SQLEXPRESS; Integrated Security=True

Do you have any concerns attaching the mdf permanently.

--
SvenC

|||Is it possible to put the mdf on a network drive? Using SQL Server Management Studio I can't: it lets me attach only mdf located in SQL Server data directory...|||

It would not be advised to have the file on a network share... I have not tested it, but I would think that the SQL Engine will not be able to connect to it (Using the T-SQL or GUI).

|||

It might work if you change the SQL service account, so that SQL Server runs in an account which has access rights to that share. Local System can only access local resources.

But you add a dependency, that the file server has to be always online otherwise SQL is down.

--
SvenC

|||

http://cyberblox.spaces.live.com/blog/cns!D0847CCE32E9987C!185.entry