1

I'm currently experiencing some trouble, seemingly because my application refuses to connect to the database if there is already another connection open from the same user (but in a separate process).

Based on my observations, it doesn't seem to matter how many or few other users are connected, and @@MAX_CONNECTIONS is set to 0 (unlimited).

Is there any way such a connections-per-user limitation may have been defined on the database, and if so, how can I turn it off?

Here is my connection string:

[db]
sql=MS SQL
provider=SQLOLEDB.1
data source=foo\bar
connection=Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=foobar

As you might be able to discern from the above, we are connecting to the database using Microsoft OLE ADO. The application is an old VB6 legacy program.

By "refuses to connect" I mean that the method Open on the ADODB.Connection object fails.

Sadly, there is currently no log of the error output from this method in the production environment. I'm currently waiting for a patch to the application to be applied, to see if I can get extended information here.

2
  • 1
    I've never seen this controlled at the database level, almost always handled in the application. I have been in your situation where I've researched for days trying to figure it out, come to find out the app was limiting to one connection per userid/logonid.
    – Jason B.
    Aug 30, 2016 at 12:49
  • Is there any LogOn trigger in your DB Server. Use this query to find that out. select t.name , t.object_id , t.is_disabled , te.type_desc FROM master.sys.server_triggers t INNER JOIN master.sys.server_trigger_events te ON t.object_id = te.object_id Aug 30, 2016 at 18:04

2 Answers 2

-1

Is there any way such a connections-per-user limitation may have been defined on the database, and if so, how can I turn it off?

You are using Microsoft SQL-Server. You can limit the Resources with the Resource Governor. Have a look at the Microsoft Documents. There you can see which options might be set and you should check. If there is a limitation, you may find it there.

Documentation: https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor

-2

I think you may be able to turn MARS off to resolve this. I had the same scenario with an application, and turning MARS off disallowed starting a new query before finishing the old one.

Try Adding this to your ConnString and resetting the connection:

MultipleActiveResultSets=false

Data Source=MSSQL; Initial Catalog=AdventureWorks; Integrated Security=SSPI; MultipleActiveResultSets=false

Link to article on MARS

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.