Connection time

[chapran]

My VB6 application can connect to the production database located in another state via internet or to the database located on my developement machine.
From my point of view the time to establish connection should be less when the local database is used. But in reality the connection to the remote database takes about 2 seconds, the time for the local database is about 15 seconds.
I have the same difference on 3 development machines.
I'm using ADO, connection strings are very similar:
Local:
strConnectionString = "DRIVER=SQL Server;SERVER=(local);UID=;DATABASE=SRSN;Trusted_Connection=Yes"

Production:
strConnectionString = "DRIVER=SQL Server;SERVER=RSRNJCOOR;UID=coord;DATABASE=SRSN;pwd=mypass"

My knowledge in configuration SQL Server is very limited.
Any advice please.

[Arjay]

Locally, you are using a trusted connection which is different than production. What happens when you make this the same?

[chapran]

When I use the same format for the connection string for local server
strConnectionString = "DRIVER=SQL Server;SERVER=(local);UID=coord;DATABASE=SRSN;pwd=mypass" I get an error:


What is correct connection string?
I'm using local copy of the production database. I do a new copy pretty often. With my current connection string I have an issue with the connection opening time only. I would not want to change the string and make me to do some additional changes in the database every time I create a new copy.

Thank you

[VictorN]

When I use the same format for the connection string for local server
strConnectionString = "DRIVER=SQL Server;SERVER=(local);UID=coord;DATABASE=SRSN;pwd=mypass" I get an error:

Well, this error message is pretty clear: just report your DB admin!
I guess, your local SQL Server doesn't have/know an account with SQL login user name "coord" and password "mypass".

[chapran]

Thank you.
"Report to your Administrator" is the error handler I wrote. In MSSQL Management studio I found coord user, I reentered the password for it and the error message changed. Now it says that the account is disabled.

I see coord user in 2 places in MSSQL Management Studio:

I opened coord properties:


What and where else should I check in order to make the connection string work with similar to production server format?

Thank you

[chapran]

In my initial posrt I gave the database with different name. In reality the same correct database is used for both connections.

[Arjay]

As I mentioned in my original reply, you are use different forms of authentication when connecting to the database (i.e. local using trusted vs. sql user/pw).

To track this down, you are going to have to connect with the same form of authentication (trusted or sql user/pw) to rule out if it's a local connectivity issue or a difference in authentication issue.

This isn't a code problem.