I cannot connect to our MS SQL 2019 server, no matter what database, always getting timeout after 5.0 seconds. If I purposefully put in the wrong password, I do get an event in the sql log saying invalid login, so I know it's hitting the server. We are running on the standard 1433, I've tried putting it in, not putting it in. I've tried connecting to the availability group and straight to the server, all no luck. Any ideas?
{
"browser-info": {
"language": "en-US",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:89.0) Gecko/20100101 Firefox/89.0",
"vendor": ""
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "1.8.0_292-b10",
"java.vendor": "Red Hat, Inc.",
"java.vendor.url": "https://www.redhat.com/",
"java.version": "1.8.0_292",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "25.292-b10",
"os.name": "Linux",
"os.version": "3.10.0-1160.25.1.el7.x86_64",
"user.language": "en",
"user.timezone": "America/New_York"
},
"metabase-info": {
"databases": [
"sqlserver",
"h2",
"mysql"
],
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MariaDB",
"version": "10.4.19-MariaDB"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.6.2"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.38.4",
"date": "2021-04-08",
"branch": "release-x.38.x",
"hash": "057b6bb"
},
"settings": {
"report-timezone": "US/Eastern"
}
}
}
Hi @michaelgillum
Don't you see any errors on MSSQL, when it's giving a timeout?
The MSSQL driver was upgraded in 0.39.0 to the latest, so I would recommend you give that a try.
Remember to backup the application database before upgrading.
https://github.com/metabase/metabase/releases/latest
While I can see you're using the latest Java 8, I would still recommend using Java 11 instead. I doubt it will change anything about your specific problem, but I have seen a few other issues, which only existed on Java 8.
https://www.metabase.com/docs/latest/operations-guide/java-versions.html
Thanks for the reply. I just ran a trace with profiler, I can see it login, it runs "exec sp_executesql N'SELECT 1'" and the logs back out and I get the timeout error. I can't move to 0.39.0 because of this problem - SQL Queries Broken in 0.39.0.1
@michaelgillum Okay, then that seems like a privileges issue, since it's not getting a return on the check.
Try adjusting the privileges for the credentials that Metabase uses on your MSSQL.
Otherwise, try the newer version, just to see if the initial connection works, since we're looking into the regression and a workaround is to use a SP instead of multi-statements in the Metabase.
Thank you, that was it. Interestingly, I copied the mappings from our SQL 2014 server - on the database it had public and MonitorData_ROLE. When I gave it db_datareader it now works. It wasn't needed on SQL 2014.
@michaelgillum Yeah, security has finally gotten a nice bump in later SQL versions, but it causes some upgrade pains. I remember upgrading a big system from 2008 to 2016 - while all data was there and working in MSSQL, but none of the legacy systems were able to connect to the database or make queries without borking. The entire migration took almost two months before I felt confident, and then I switched over-night without anyone noticing anything (besides much faster performance).