asked on

Problem Sharing A MS SQL Server 2012 Express Database with Multiple VB 2013 Users on a Private Network

I have an application written in Visual Basic 2013. The application maintains a SQL Server 2012 Express SP1 (x64) database. The computer the application is installed on (#1) and a second computer (#2) use Microsoft Windows 10 operating systems. Both computers have SQL Server 2012 Express SP1 (x64) installed and are on a local network . Computer #1 runs the application locally (from it's 'C' drive) and computer #2 runs the application through the network. Both computers can run the application successfully provided only one computer is running the application at a time. The problem occurs when either computer #1 or #2 is running the application (which wil run successfully) and the application is started on the other computer the 2nd application run will fail because it is not able to open the database. The error message is as follows:
'Unable to open the physical file "\\Desktop-uptn518\cmsrs\data\System.dbf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
An attempt to attach an auto-named database for file \\Desktop-uptn518\cmsrs\data\System.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
The connection string used was
"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\data\System.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
.
Any help or suggestions would be greatly appreciated. Thanks in advance.

SOLUTION

Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lschwabe

ASKER

I appreciate all the information that I have received so far. Thank you. I am fairly new to SQL Server 2012 Express and it will take a few days for me to go through the information that I have receives so far and apply it to my application. The application had been tested on a single computer using SQL Server 2012 LocalDB. My objective right now is to modify it so that multiple users can access a common database on a Windows 10 'Home group network'.

Computer #2 ran the application (installed on computer #1) through the network. I had installed SQL Server 2012 Express on computer #2 because if I didn't the application couldn't access the database on computer #1, however the database could only be accessed if the application was not running simultaneously on computer #1.

Hopefully the information I have received so far will resolve my problem and I will be ready then to close the case.

Thanks again.

lschwabe

ASKER

So far I have been following the Youtube video (as suggested by pcelba) to configure the SQL Server to allow remote connections. Everything went well until I tried to connect to the SQL Server 2012 (installed on computer 2) using the SQL Server 2012 Management Studio that was installed on computer 1.

It connected when I used the following:

Server Type Database Engine
Server Name 999.999.9.999,99999
Authentication Windows Authentication
User name xxxxxxxxxxxxxxx
Password xxxxxxxxxxxxxxx

When I tried to connect with the following:

Server Type Database Engine
Server Name 999.999.9.999,99999
Authentication SQL Server Authentication
Login sa
Password xxxxxxxxx

(The Login poperties for use 'sa' were set for 'SQL Server Authentication', The check box for 'Enforce password policy' was checked.)

It failed with the following error message:

Cannot connect to 999.999.9.999,99999
Additional Information:
Login failed for user 'sa'. (Microsoft Server, Error: 18456)

Any help or suggestions would be appreciated. Thanks

SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lschwabe

ASKER

The problem I was having should now be resolved. There is still testing that I have to do with the VB application but I am sure the information that I have received so far will be sufficient. Thank you all again for your suggestions.