Add new MSSQL driver to support Windows Integrated Security

[tmknight]

Describe the bug
Trying to leverage integrated domain security to avoid hard-coded passwords. The following connection has been created though results in untrusted domain with integrated security" error.
Similar, though different, to: #70

"name": "the-name",
"server": "the-server",
"dialect": "MSSQL",
"port": 1433,
"database": "the-db",
"username": "the-user",
"askForPassword": false,
"password": "",
"domain": "the-domain",
"connectionTimeout": 15

Error opening connection Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.

INFO: Executing SQLTools.selectConnection ERROR: Error: { [Error: Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. at c:\Users\user\.vscode\extensions\mtxr.sqltools-0.21.0\extension.js:1:22985 at c:\Users\user\.vscode\extensions\mtxr.sqltools-0.21.0\extension.js:1:23392 at Immediate.messageQueue.size.setImmediate (c:\Users\user\.vscode\extensions\mtxr.sqltools-0.21.0\extension.js:1:23936) at runCallback (timers.js:694:18) at tryOnImmediate (timers.js:665:5) at processImmediate (timers.js:647:5)] code: -32001, data: { dialect: 'MSSQL', dialectOptions: {} } }

Tried adding the domain to trusted domains JSON, to no avail.

Expected behavior
Connect to DB without hard-coded credentials

Desktop (please complete the following information):

• SQLTools: 0.21
• VSCode: 1.39.2
• Dialect: MSSQL
• OS: Windows 10 x64 1903

[mtxr]

@tmknight I'll release a new version with some MSSQL updates tonight.

Try to take a look at http://tediousjs.github.io/tedious/api-connection.html and also #375

I believe you can achieve this with similar settings.

Problem is I can't test this scenario right now, but I'll try to find a way to reproduce it, ok?

[tmknight]

Thanks for digging into the problem.
Tried adding "type": "ntlm", though still no joy unless I populate a username and password.
FWIW, Microsoft's SQL extension is handling integrated auth without issue.

To add, I also had earlier tried a connection string and that too resulted in error.

Thanks, again for delving into a solution.

[jbunselmeyer]

@tmknight You mention the Microsoft SQL extension is handling integrated auth without issue. I'm needing to try to utilize it to use my domain account for some of my server connections for audit reasons.

Is there a specific section you add in the config to make it work or is it a specific syntax in the mssql.connections section of the config that that specifies additional values?

Thanks!

[tmknight]

The selection is simply "integrated" as you step through establishing a new connection. This is what the end result looks like in the JSON:

"mssql.connections": [
    {
        "server": "the_server",
        "database": "the_db",
        "authenticationType": "Integrated",
        "password": ""
    }
]

I may have tried that for "SQL Tools", though can't precisely recall. I'll give it a go shortly.

Thanks for continuing to look into this...

[jbunselmeyer]

@tmknight Does it then prompt you for the domain at some point when you connect?

[tmknight]

It does not. These are the server specific prompts:

[jbunselmeyer]

How are you logging in with a domain account then? That's where I'm confused. I want to connect via a domain account. When I use SSMS I have a RunAs shortcut that specifies the domain credentials to use. I'm trying to figure out how accomplish the same thing using these SQL Server extensions.

[tmknight]

Integrated authentication is predicated on the user being logged onto a domain joined device with a domain account. Also the SQL server is domain joined and permissions are set leveraging Active Directory Users/Groups.

Thus no user information is required when setting up a connection with integrated authentication.

Hope that makes sense.

[tmknight]

I'm joined to a Windows Active Directory domain. To logon to my Windows PC, I authenticate with my domain user account. From there kerberos tokens and ntlm take care of any other authentications on other domain joined resources. Not sure how else to describe it.

[mtxr]

Thanks for digging into the problem.
Tried adding "type": "ntlm", though still no joy unless I populate a username and password.
FWIW, Microsoft's SQL extension is handling integrated auth without issue.

To add, I also had earlier tried a connection string and that too resulted in error.

Thanks, again for delving into a solution.

So if you fill username and password it does work? It does look with a similar issue we've got before. Let me see if I can find it.

Also, @ransagy do you think this is somehow similar to what you've got in your setup?

[mtxr]

Actually the driver we are using (tedious) doesnt support integrated security as we have seem in other tools. See tediousjs/tedious#836
I've found another driver that supports, it's https://www.npmjs.com/package/msnodesqlv8, but we would need to add that new driver to SQLTools.

We have a tool to add new drivers, also the docs, would you like to add it? would require you to work in the queries to fetch databases and columns, but I'm pretty sure we could use the same queries from the other driver.

Add new driver guide: https://vscode-sqltools.mteixeira.dev/contributing/support-new-drivers

[mtxr]

@tmknight @jbunselmeyer could you please move this to a different thread inside of the correct repository? With the exception of the first comments, everything else is all about vscode-mssql. They use proprietary driver/tools to achieve what you are looking for, something we currently not support. Let's try to keep this thread useful for adding this feature to SQLTools.

Don't get me wrong, it's just hard to keep track of everything and having to filter unrelated comments when working on feature requests

[jbunselmeyer]

Absolutely @MxTR, totally understand. I take responsibility for taking it off-topic, sorry about that.

[mtxr]

If anyone else want to try to integrate this with the extension, here is the guide to achieve that: https://vscode-sqltools.mteixeira.dev/contributing/support-new-drivers

I'll close this issue for now