ORM fails to connect - Login error with MSSQL #4844

@lunny commented on GitHub (Feb 13, 2020):

Which latest version you can work with this MSSQL instance?

@lunny commented on GitHub (Feb 13, 2020): Which latest version you can work with this MSSQL instance?

GiteaMirror commented

@commel commented on GitHub (Feb 13, 2020):

@lunny: Gitea 1.10.3 works fine

@commel commented on GitHub (Feb 13, 2020): @lunny: Gitea 1.10.3 works fine

GiteaMirror commented

@guillep2k commented on GitHub (Feb 13, 2020):

Just in case. Try:

HOST     = host\\dbinstance

@guillep2k commented on GitHub (Feb 13, 2020): Just in case. Try: ``` HOST = host\\dbinstance ```

GiteaMirror commented

@commel commented on GitHub (Feb 14, 2020):

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

@commel commented on GitHub (Feb 14, 2020): @guillep2k thanks, i gave it a try, sadly that notation does not work either with 1.10.3 or 1.11

GiteaMirror commented

@c-key commented on GitHub (Feb 14, 2020):

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

@c-key commented on GitHub (Feb 14, 2020): 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

GiteaMirror commented

@lunny commented on GitHub (Feb 14, 2020):

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

@lunny commented on GitHub (Feb 14, 2020): I think this should be a configuration problem, because CI with mssql integration tests succeed.

GiteaMirror commented

@c-key commented on GitHub (Feb 14, 2020):

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 commented on GitHub (Feb 14, 2020): > 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.

GiteaMirror commented

@c-key commented on GitHub (Feb 17, 2020):

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

@c-key commented on GitHub (Feb 17, 2020): > 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

GiteaMirror commented

@lunny commented on GitHub (Feb 18, 2020):

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

@lunny commented on GitHub (Feb 18, 2020): I think it maybe related with we have upgraded mssql driver on v1.11.0

GiteaMirror commented

@cht47 commented on GitHub (Mar 2, 2020):

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.

@cht47 commented on GitHub (Mar 2, 2020): 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.

GiteaMirror commented

@guillep2k commented on GitHub (Mar 3, 2020):

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

@guillep2k commented on GitHub (Mar 3, 2020): 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 ```

GiteaMirror commented

@commel commented on GitHub (Mar 11, 2020):

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.

@commel commented on GitHub (Mar 11, 2020): 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.

GiteaMirror commented

@stale[bot] commented on GitHub (May 10, 2020):

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.

@stale[bot] commented on GitHub (May 10, 2020): 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.

GiteaMirror commented

@6543 commented on GitHub (May 10, 2020):

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 commented on GitHub (May 10, 2020): 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?

GiteaMirror commented

@6543 commented on GitHub (May 10, 2020):

@commel can you test current master for us?

@6543 commented on GitHub (May 10, 2020): @commel can you test current master for us?

GiteaMirror commented

@commel commented on GitHub (May 11, 2020):

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. :-(

@commel commented on GitHub (May 11, 2020): 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. :-(

GiteaMirror commented

@6543 commented on GitHub (May 11, 2020):

thanks for testing ...

@6543 commented on GitHub (May 11, 2020): thanks for testing ...

GiteaMirror commented

@guillep2k commented on GitHub (May 20, 2020):

@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?

@guillep2k commented on GitHub (May 20, 2020): @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?

GiteaMirror commented

@jeffest commented on GitHub (May 21, 2020):

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.

@jeffest commented on GitHub (May 21, 2020): 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.

GiteaMirror commented

@guillep2k commented on GitHub (May 22, 2020):

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.

@guillep2k commented on GitHub (May 22, 2020): 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.

GiteaMirror commented

@jeffest commented on GitHub (May 22, 2020):

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.

@jeffest commented on GitHub (May 22, 2020): 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.

GiteaMirror commented

@guillep2k commented on GitHub (May 22, 2020):

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

@guillep2k commented on GitHub (May 22, 2020): Did you run `sqlcmd` on the host with the database or on the host running Gitea?

GiteaMirror commented

@jeffest commented on GitHub (May 22, 2020):

On the host running Gitea

@jeffest commented on GitHub (May 22, 2020): On the host running Gitea

GiteaMirror commented

@guillep2k commented on GitHub (May 22, 2020):

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

Is this your first installation or an upgrade?
If it was an upgrade, from which version?
Does the Gitea database already exist?
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.

@guillep2k commented on GitHub (May 22, 2020): @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](https://www.wireshark.org/) 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`.

GiteaMirror commented

@jeffest commented on GitHub (May 25, 2020):

@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 commented on GitHub (May 25, 2020): @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).

GiteaMirror commented

@jeffest commented on GitHub (May 25, 2020):

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 commented on GitHub (May 25, 2020): 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.

GiteaMirror commented

@jeffest commented on GitHub (May 25, 2020):

@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 commented on GitHub (May 25, 2020): @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.

GiteaMirror commented

@jeffest commented on GitHub (May 25, 2020):

@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.

@jeffest commented on GitHub (May 25, 2020): @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.

GiteaMirror commented