Connecting db via adodb

Post your questions regarding using the Vault and Fortress API in your programs.

Moderator: SourceGear

Post Reply
epsobolik
Posts: 33
Joined: Mon Jan 07, 2008 10:19 am
Location: Lexington, MA
Contact:

Connecting db via adodb

Post by epsobolik » Tue Dec 11, 2018 9:18 am

I need to generate a backup of the Vault databases on demand. I usually use the ADODB ActiveX in JavaScript to do such things. However, when I try to open the Vault database connection, I get a 'Login failed for user epsobolik' error. Here is the code that I am using:

var cn;
cn = new ActiveXObject("ADODB.Connection");
cnStr = "PROVIDER=SQLOLEDB;SERVER=<server>.wrightsoft.com;UID=epsobolik;PWD=<password>";
cn.Open(cnStr);

I am using the username and password that the computer is logged as. When I connect to the same server in Visual Studio and use the 'Use Windows Authentication' option, it connects fine.

Has anyone else tried to do this? Any ideas?

epsobolik
Posts: 33
Joined: Mon Jan 07, 2008 10:19 am
Location: Lexington, MA
Contact:

Re: Connecting db via adodb

Post by epsobolik » Tue Dec 11, 2018 12:09 pm

Could it be that I'm trying to login using Windows credentials and the connect string is looking for database credentials? What are the database credentials?

jclausius
Posts: 3702
Joined: Tue Dec 16, 2003 1:17 pm
Location: SourceGear
Contact:

Re: Connecting db via adodb

Post by jclausius » Tue Dec 11, 2018 1:24 pm

Have you thought about using SQL Server Management Studio to create an automated task to backup the databases? It manages a lot of this for an administrator. https://support.microsoft.com/en-us/hel ... ver-manage


With that said, let's get back to the post itself. Your connect string contains a UID / PWD, so this means the JS is trying to connect to the database using SQL Server authentication. There are different types of connection string options, and you can read more about them here - https://www.connectionstrings.com/sql-server/

In regards to the connect string in the post, you'll need to :

a) Make sure you have the correct SQL Login for 'epsobolik'. See https://docs.microsoft.com/en-us/sql/re ... erver-2017

b) Ensure SQL Server allows SQL Server authentication. See https://docs.microsoft.com/en-us/sql/re ... erver-2017 for additional details.

More details about authentication - https://docs.microsoft.com/en-us/sql/re ... erver-2017

c) Finally, make sure the account you use to log into has correct permissions to perform the backup for each database. See "Permissions" section of https://docs.microsoft.com/en-us/sql/t- ... erver-2017
Jeff Clausius
SourceGear

epsobolik
Posts: 33
Joined: Mon Jan 07, 2008 10:19 am
Location: Lexington, MA
Contact:

Re: Connecting db via adodb

Post by epsobolik » Tue Dec 11, 2018 2:28 pm

Thanks for your reply. The SQL Server approach would be fine (it's actually what we are doing now) if this was the only step. There are other actions - copy files and zip everything up.

Is there a way to specify Windows Authentication rather than SQL Server authentication in a connection string? I didn't see anything about different types of authentication in the link you provided. SQL Server Authentication is NOT enabled. Just Windows Authentication.

I discovered that I can write SQL commands for SQL Server in Visual Studio Code with the SQL/MSSQL Extension. It has its own way of connecting to SQL Server databases. If I select Windows Authentication and enter the same credentials, the connection succeeds. with 4 BACKUP DATABASE commands, I can do what I want. However, there is the problem that the other copy zip commands can't be executed. I also just found about the sqlcmd utility. Hopefully, that can be used to execute the SQL commands in a JavaScript script. I need to read up on it, however.

jclausius
Posts: 3702
Joined: Tue Dec 16, 2003 1:17 pm
Location: SourceGear
Contact:

Re: Connecting db via adodb

Post by jclausius » Tue Dec 11, 2018 3:49 pm

epsobolik wrote:Is there a way to specify Windows Authentication rather than SQL Server authentication in a connection string? I didn't see anything about different types of authentication in the link you provided. SQL Server Authentication is NOT enabled. Just Windows Authentication.
Windows Auth is sometimes called "Trusted Connection". It's in the connect strings link.

Note, that a Windows Authenticated process becomes a bit tricky if you run it as a service or in any context where that running process's security context is not found as a valid Windows account configured within SQL Server.
Jeff Clausius
SourceGear

epsobolik
Posts: 33
Joined: Mon Jan 07, 2008 10:19 am
Location: Lexington, MA
Contact:

Re: Connecting db via adodb

Post by epsobolik » Wed Dec 12, 2018 1:29 pm

A combination of experimenting with SQL in Visual Studio Code and using SQLCMD in a JavaScript script worked. SQLCMD interprets no authentication as a 'Trusted Connection'. It works now. Thanks for your help.

jclausius
Posts: 3702
Joined: Tue Dec 16, 2003 1:17 pm
Location: SourceGear
Contact:

Re: Connecting db via adodb

Post by jclausius » Thu Dec 13, 2018 8:30 am

Great. I'm glad you were able to find a solution.
Jeff Clausius
SourceGear

Post Reply