I have installed a program that utilizes SQL Server 2012 on five computers. The computers are in a domain, each user has a unique id. I did not write the program, I am only configuring it. The publisher does not support this setup but has stated others have done it successfully. I do not know who they are so I cannot reach out to them. The server is on one of the computers and not the domain controller. The program has a database connection string editor where the location and user information is stored. I set the permissions for the directory where the mdf files are stored to allow the users access, and mapped drives to each computer.
Everyone connects fine. Except when someone else is already connected. On occasion I've had multiple connections but it seems random. Usually I receive an error stating permission is denied for the user to the main database. Multi access is enabled on the database. Do I need to do something with users or roles in the server management tool? The server was installed as Microsoft Integration only. I've also tried to make changes using the management tool only to be denied due to lack of permissions. I've googled my way through some of this but I am not finding much that just spells it all out.
Hi Vince,
Is there any error in the SQL error log? Is all 5 computers using the same login ID to access the DB? Hence got that issue, have you try to use 5 different account to access the DB and see if that works (I do not see the difference, but worth a try).
If you thinks is a DB multi access issue, you might can try to have 5 different computers, using the same ID connection to the DB from other means, eg. sqlcmd or SSMS and each runs some queries. If that works, which means the DB can accept more then 1 connection and it might means is related to something else.
You mean in the connection string, it uses AttachDbFilename ? If not mistaken that is or SQL Express, and it is single user mode. Not sure it will even works for other edition of SQL Server.
For multi-user access of SQL Server, typically we use Instance Name or IP on the connection string
Yes, it is Express 2012. The connection string examples are helpful, I'll have to try some things when I have a chance.
I have read what I could find from Microsoft and multi user mode is supposed to be a feature with Express, and I've found the setting within the management tool.
No errors. They use Windows Authentication to log in. When the login fails the reason is posted on the screen saying permission is denied for that user. But that problem only happens when another is logged in. It's as if the DB is getting locked? But it's not consistent. I've had, on a few attempts, multiple users connected and reading tables.
Yes. It is supported. But using AttachDbFilename might cause it to be in single user mode.
SQL Server is unlike MS Access, accessing it should goes via the SQL Native Client. Using AttachDbFileame to access it might result it single user access only. I am not 100 % sure on this but think i came across something about this ages ago.
Try to change the connection string to use IP or instance name.