ORM fails to connect - Login error with MSSQL

[commel]

• Gitea version (or commit ref): 1.11.0 release

• Git version: 2.24.0.windows.1

• Operating system: Windows Server 2019 Datacenter Version 1809

• Database (use [x]):

  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite

• Can you reproduce the bug at https://try.gitea.io:

  • Yes (provide example URL)
  • No
  • Not relevant

• Log gist:
  https://gist.github.com/commel/4f0d03f6f82b25688fb32f1cf2f2a8e8

Description

Starting gitea fails with 1.11.0, the log claims that the password to the MSSQL server is incorrect. Login and Deploy with same config works on previous releases 1.10.3 and 1.9.6. Login with the gitea credentials also work on MSSMS.

This is the configuration:

[database]
DB_TYPE  = mssql
HOST     = host\dbinstance
NAME     = gitea
USER     = gitea
PASSWD   = xxxx
SSL_MODE = disable

[bagasme]

@commel can you connect to DB host?

[lunny]

What's your MSSQL server version?

[commel]

@lunny The connected server is a SQL Server 2014 (Version 12.0.2000.8)
@bagasme As I wrote: I can connect with older gitea versions, and with Microsoft SQL Server Management Studio using the credentials in the gitea.ini.

[lunny]

Which latest version you can work with this MSSQL instance?

[commel]

@lunny: Gitea 1.10.3 works fine

[guillep2k]

Just in case. Try:

HOST     = host\\dbinstance

[commel]

@guillep2k thanks, i gave it a try, sadly that notation does not work either with 1.10.3 or 1.11

[c-key]

I have the same issue. Gitea can not connect to our MS SQL express database.

[database]
DB_TYPE  = mssql
HOST     = 127.0.0.1\dbinstance
NAME     = gitea
USER     = gitea
PASSWD   = xxxx
SSL_MODE = disable

Git-Version: 2.25.0
Gitea-Version: 1.11.0
OS-Version: Windows Server 2016 1607

I connection through MS SQL Server Management Studio is possible. I tried in the app.ini from Gitea the host-string "machinename\dbinstance", ".\dbinstance" and also th tip with the escaped "". Nothing works.
Maybe it depends to #8536

[lunny]

I think this should be a configuration problem, because CI with mssql integration tests succeed.

[c-key]

I think this should be a configuration problem, because CI with mssql integration tests succeed.

Which configuration is set on CI with mssql integration? So we can check our configuration on mssql and server side.

[c-key]

I think this should be a configuration problem, because CI with mssql integration tests succeed.

But why it is woking with the previous version of gitea under 1.11.0

[lunny]

I think it maybe related with we have upgraded mssql driver on v1.11.0

[cht47]

1.11 also doesn't start with PostgreSQL 10 on the same machine. (CentOS 7)
Different DB, different OS, this is a major issue and should be fixed soon.

[guillep2k]

I'm using PostgreSQL 9.6.16 on CentOS 7 and 1.11 works fine for me. I'm using:

[database]
DB_TYPE  = postgres
HOST     = 127.0.0.1:5432
NAME     = gittest
USER     = (secret db user)
PASSWD   = (secret db passwd)
SSL_MODE = disable

[commel]

Just wanted to let you know that the problem remains with the newest updates of Gitea 1.11.3. The current update of the 1.10-branch to 1.10.6 still works.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. I am here to help clear issues left open even if solved or waiting for more insight. This issue will be closed if no further activity occurs during the next 2 weeks. If the issue is still valid just add a comment to keep it alive. Thank you for your contributions.

[6543]

gitea 1.10 branch use:

github.com/denisenkom/go-mssqldb v0.0.0-20190924004331-208c0a498538

gitea >= 1.11 use:

github.com/denisenkom/go-mssqldb v0.0.0-20191128021309-1d7a30a10f73

so an update may help?

[6543]

@commel can you test current master for us?

[commel]

Sure, happy to help. Just tried the master binary from 05/11/2020 12:51:35 AM +00:00, but this one still has the same problem. :-(

[6543]

thanks for testing ...

[guillep2k]

@commel Some users could fix this issue by changing the connection string escaping the backslash (e.g. 127.0.0.1\dbinstance --> 127.0.0.1\\dbinstance); this is due to a library update we did a while ago. Can you check if this is your case?

[jeffest]

just downloaded and launched v1.12. Using MSSQL 2016 with named instance is having the same issue. Using the IP or escaping the backslash didn't help.
Is there anyway to have a detailed log ? Tried to put XORM log level to Debug but that didn't help.

[guillep2k]

Have you tried with sqlcmd from the command box to see what happens?:

sqlcmd -S tcp:127.0.0.1\dbinstance -U username -P password -Q "select 'hello'"
or
sqlcmd -S tcp:127.0.0.1 -U username -P password -Q "select 'hello'"

The first version should work if the instance name is not the default from SQL Server (e.g. not SQLEXPRESS). Otherwise, the second version should work and you should be using just HOST = 127.0.0.1 (with no instance specified).

I know you've already tried with SQL Management Studio, but that test is indeterminate because it tries different routes (named pipes, etc.), and we want to check only if this particular route works.

[jeffest]

I just did and it works just fine. Compared to your guess above (and that might help you), my SQL Server is not on localhost and this is not a SQLEXPRESS edition.
Thank you anyway for your help.

[guillep2k]

Did you run sqlcmd on the host with the database or on the host running Gitea?

[jeffest]

On the host running Gitea

[guillep2k]

@jeffest Since you're not the OP of the issue, let me ask a couple of questions:

1. Is this your first installation or an upgrade?
2. If it was an upgrade, from which version?
3. Does the Gitea database already exist?
4. What is the error you're getting in Gitea's log?

Finally... it would be great if you could use a tool like Wireshark to spy the network and check if a connection is being attempted or not even that (you can repeat the sqlcmd test to have a sample of what the connection attempt should look like). The filter you're interested in is tcp.port == 1433.

[jeffest]

@guillep2k, it's a brand new instance and, btw, I'm brand new to Gitea.
Yes the database already exists along with a dedicated user and associated access rights.
As for the logs, even though I was in DEBUG level they were pretty useless as none of the logged event were coming from the MSSQL driver itself. But It is very likely that I did not configure the Gitea's logging feature properly.
Also, I had to move on for my project, so I switched to mysql driver (all good first try !). But, I will install another instance of Gitea on the same machine and will get you the logs. I will also try the network sniffing.
What I can tell you for sure is that a connection is in fact attempted. To test that, I removed the db's username and password to "force" the driver to go with Windows authentication. Which it did and the error message was different this time (fyi, my Windows user is sysadmin on the SQLServer).

[jeffest]

Here is what I get in Gitea's log :
2020/05/25 09:33:57 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] select name from sysobjects where xtype ='U' [] - 90.9985ms /go/src/code.gitea.io/gitea/vendor/xorm.io/xorm/core/db.go:154 (0xb92d2b) /go/src/code.gitea.io/gitea/vendor/xorm.io/xorm/dialects/mssql.go:408 (0xba22ee) /go/src/code.gitea.io/gitea/vendor/xorm.io/xorm/session_schema.go:236 (0xc143e9) /go/src/code.gitea.io/gitea/models/models.go:163 (0x107750a) /go/src/code.gitea.io/gitea/routers/install.go:165 (0x1976292)

From the UI, with "forced" Windows Authentication I get the following message :

The database settings are invalid: Login error: mssql: Cannot open database "gitea" that was requested by the login. Using the user default database "master" instead.

With login/password I get the following:

The database settings are invalid: Login error: mssql: Login failed for user 'gitea'.

This leads me to another possibility. The server I'm trying to connect to is hosting multiple versions of SQL Server. The default instance is SQL Server 2014, then I have named instances for 2008, 2012 and 2016. I was wondering if the MSSQL driver was compatible with the dynamic port assignment technique used by SQL Server.

[jeffest]

@guillep2k, I think my assumption above is correct. The driver only connects to SQL Server on port 1433 which in my case is the default SQL Server instance (i.e. not the one I target). I've checked the logs on that server and I saw the failed login attempts for user 'gitea'.
To go further, I retrieved the dynamic port number used by my named instance and passed it to Gitea's install screen in this format: <server>\<instance name>:<port> and this time Gitea successfully logged on and completed the installation process.
As SQL Server will always use the same dynamic port unless already in use when the engine start, I'll keep my config as-is for now.
As for the GO mssql driver, it should be aware of possible use of dynamic port assignment and first query the SQL Server's Browser service to obtain the port number used by the named instance.
Let me know if you need further details. If I can provide them I'll be happy to help.

[jeffest]

@commel, can you please try to obtain the port number used by your instance of SQL Server and try to put that port number in Gitea's ini file ?
HOST = host\dbinstance:port
I just want to find out if my findings will help you out or if our problems have different sources.
Obviously in my case I cannot test with a previous version of Gitea as this is my first install.

[guillep2k]

@jeffest Good hunting! I'd never have thought of a multiple instances scenario. 👍

[commel]

@jeffest Great Catch! That did it! The company server in fact has multiple SQL server instances running. I have no direct access to the server itself to find the port so I connected with MSSQL Management Studio and queries the master database with:

SELECT DISTINCT 
    local_tcp_port 
FROM sys.dm_exec_connections 
WHERE local_tcp_port IS NOT NULL

which i added to the gitea.ini:

HOST     = my-hostname\MYINSTANCENAME:49555

I was able to successfully connect. Then I upgraded to my last tried version (to verify this is the fix and not some update from a later version), which was 1.11.3. Then I've upgraded to 1.11.5.

All running now, thank you very much, everybody, specially @lunny, @guillep2k and @jeffest!