Having a devil of a time connecting to SQLEXPRESS. Troubleshooting logs extensive but do not seem to contain any red flags. Referred to Trouble connecting to SQLEXPRESS data without success. Does anything obvious jump out in the below connection that's not right, suggestions?
Database type: SQL Server
Name: Evaluation
Host: localhost
Port: 1433
Database name: MyDatabase
Database Instance name: SQLEXPRESS
Username: sa
Password: ****
Metabase v0.39.4
Windows 10
Hi @mesquest
That looks correct, but check if MSSQL is actually running on port 1433 and is listening on localhost.
There's a lot of anti-malware that does "magic", so it might be blocking ports - try disabling that.
Thanks @flamber. Got my MSSQL databases in DBeaver.io once holes were drilled thru the Norton 360 firewall and TCP/IP enabled via the SQL Server Configuration Manager. Now instead of a timeout I'm getting a connection failure. So I know communication isn't the issue, I suspect it's the Username: sa and Password ****.
2021-07-08 20:59:21,126 DEBUG middleware.log :: POST /api/database 400 63.0 ms (0 DB calls)
{:valid false,
:dbname "Login failed for user 'sa'. ClientConnectionId:f1ef56f0-d732-4a64-8e98-41e717d97311",
:message "Login failed for user 'sa'. ClientConnectionId:f1ef56f0-d732-4a64-8e98-41e717d97311"}
This is what it looks like logging into the Microsoft SQL Server Management Studio but I'm at a loss to tap it thru to Metabase with the password to my laptop (that's what worked on my tower):
@mesquest You're showing that you're connecting with winauth in SMS, so can you connect with the user sa directly there. Otherwise that's your problem - which is why Metabase cannot connect either.
Also, there might be a difference between localhost and the computer hostname.
@flamber, is it possible that Micrsoft SQL Server 2019 is to blame for Metabase not reading the specified database? I've moved away from Windows Authentication to a Username & Password to try and get a handle of what's going on.
An incorrect UN or PW yields the error message:
Login failed for user 'Username'. Reason: The password of the account must be changed
A correct UN and PW yields the error message:
Cannot open database "MyDatabase" requested by the login. The login failed.
@mesquest The first error looks like a configuration problem:
https://serverfault.com/questions/66853/login-failed-password-of-account-must-be-changed-error-18488
The second error looks like a privilege problem:
https://stackoverflow.com/questions/30212318/cannot-open-database-mydb-requested-by-the-login-the-login-failed-login-fail
SQL Express can be quite annoying, since it defaults to Windows authentication, which needs to be changed to user authentication, but then already created databases might need to have their privileges adjusted.
I know that 2019 is supported, since that's essentially what Azure is running and Metabase can connect to that. But I only have 2017 setup locally.
Thanks @flamber, the problem was permissions from within Microsoft SQL Server Management. Right click on a database then select Properties>Permissions and grant Connect, View, etc. using the grid at the lower right. That's on top of setting ports to 1433 instead of dynamic and kicking a hole thru the Norton firewall... All told it took 3 days of noodling around to find all this, never had to get this explicit before, hopefully this will save someone a bit of time getting their DBA hats on!
