Connecting db via adodb

[epsobolik]

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]

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]

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

[epsobolik]

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]

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.

[epsobolik]

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]

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