Shared LocalDB .mdf database in Local Network

Question

Shared LocalDB .mdf database in Local Network

NadineBen

Mr or Mrs. 500

Points: 524
More actions
March 10, 2017 at 7:24 am

#321325

I would like to share the .mdf database (one database) of my c# application located on Computer 1 on a local network (5 computers).
I am using localdb sql server 2014 installed in all those computers.
When I try to access to the .mdf database from any computer (2,3,4,5) it works but it does accept only one connection. When I try to connect simultaniously from another computer or even from computer 1 (where the database is hosted), I fot an error message : Login failed for user .../... Error Number : 4060, state: 1, Class: 11
Please Help. Thank you.
Création Site Web - Boite Communication Alger

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

If you look in the errorlog of the server (assuming you're capturing failed logins in the errorlog), that will give you more details on why the logon failed.

John

Rob Buecker SSChasing Mays Points: 639 More actions · Answer 2

What user mode is the database in?
SELECT d.name , d.user_access_desc FROM sys.databases AS d ;

NadineBen Mr or Mrs. 500 Points: 524 More actions · Answer 3

Rob Buecker - Friday, March 10, 2017 8:23 AM
What user mode is the database in?
SELECT d.name , d.user_access_desc FROM sys.databases AS d ;

MULTI_USER

Création Site Web - Boite Communication Alger

Rob Buecker SSChasing Mays Points: 639 More actions · Answer 4

Are you connecting the computer 1 instance? Or are you attempting to use the same .mdf file as on multiple instances (2, 3, 4, and 5)?
Each SQL Server instance would need its own version of the file.

NadineBen Mr or Mrs. 500 Points: 524 More actions · Answer 5

Rob Buecker - Friday, March 10, 2017 8:31 AM
Are you connecting the computer 1 instance? Or are you attempting to use the same .mdf file as on multiple instances (2, 3, 4, and 5)?
Each SQL Server instance would need its own version of the file.

Yes I do. I would like to use the same database file, so the other computers could insert, update, remove data from that database.

Création Site Web - Boite Communication Alger

Lowell SSC Guru Points: 323518 More actions · Answer 6

SQLLocalDB is a special version of an MDF for SQL, it does not allow remote connections, and only one person can open the file at a time.

if you need a shared database, install SQLExpress on a machine, enable remote connection and TCP(which is disabled by default), and allow your users to connect to that instance isntead.
SQL Express is free, and it's limited to a single CPU and ten gig database size; fine for a startup , shared database until you are ready to upgrade.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

NadineBen Mr or Mrs. 500 Points: 524 More actions · Answer 7

Lowell - Friday, March 10, 2017 8:37 AM
SQLLocalDB does not allow remote connections, and only one person can open the file at a time.
if you need a shared database, install SQLExpress on a machine, enable remote connection and TCP(which is disabled by default), and allow your users to connect to that instance isntead.
SQL Express is free, and it's limited to a single CPU and ten gig database size; fine for a startup , shared database until you are ready to upgrade.

Thank you
I am trying to install the package from : https://www.microsoft.com/fr-fr/sql-server/sql-server-editions-express (hope it's the right one)

What's next, shall I create a new database on SQLExpress and modify connection string in my c# app?

Création Site Web - Boite Communication Alger

Lowell SSC Guru Points: 323518 More actions · Answer 8

after you install, then you probably need to do the following:
run the SQL configuration tool and enable TCP IP
enable remote connections on the instance
Add Logins to your Instance, whether windows or SQL

create your database, and add the logins you created as USERS in the database
note the name of your instance, it might be MachineName\SQLExpress or MachineName\SQL2016 or soemthing.

change your connection string in your application and test!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!