Enable IntegratedAuthentication (ActiveDirectory) for MSSQL

[road42]

• Gitea version (or commit ref): latest
• Git version: 2.19.1
• Operating system: Windows Server 2012 R2
• 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:

Description

I'd like to use the integrated authentication for a mssql database. I already looked into the sourcecode:

On this line the connectionString is created:

gitea/models/models.go

Line 239 in 127f477

connStr = fmt.Sprintf("server=%s; port=%s; database=%s; user id=%s; password=%s;", host, port, DbCfg.Name, DbCfg.User, DbCfg.Passwd)

It seems that gitea uses xorm as or-mapper.
xorm uses the mssql-driver from: https://github.com/denisenkom/go-mssqldb

The driver has the following documentation:

user id - enter the SQL Server Authentication user id or the Windows Authentication user id in the DOMAIN\User format. On Windows, if user id is empty or missing Single-Sign-On is used.

Solution?

I already tried to leave "user id" and/or "password" in the configuration empty, but it did not connect to the database.

It should be possible to leave the password and username field empty to connect to a mssql database.

[lafriks]

I don't think driver supports integrated authentication

[lafriks]

To use AD auth you still need to provide username and password

[road42]

I created some test code which uses xorm to select the username and the version of a sql-server.
Sorry, if the code isn't the nicest one, I never written go before.

I connected only using server=<server>;database=<database> and I got returned
the correct username and version.

package main

import (
	"fmt"
	"log"

	_ "github.com/denisenkom/go-mssqldb"
	"github.com/go-xorm/core"
	"github.com/go-xorm/xorm"
)

var engine *xorm.Engine

func main() {
	var err error
	var sql string
	var res []map[string]string

	engine, err = xorm.NewEngine("mssql", "server=<server>;database=<database>")
	if err != nil {
		log.Fatal(err)
	}
	engine.ShowSQL(true)
	engine.Logger().SetLevel(core.LOG_DEBUG)

	sql = "select current_user as usr,@@version ver"
	res, err = engine.QueryString(sql)
	if err != nil {
		log.Fatal(err)
	}

	for _, element := range res {
		// index is the index where we are
		// element is the element from someSlice for where we are
		fmt.Println(element)
	}
	engine.Close()
}

result

map[usr<winUsername> ver:Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)
        Sep  7 2018 01:37:51
        Copyright (c) Microsoft Corporation
        Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
]

It would be nice if I could use this in gitea, too.

[lunny]

So if there is an config on ini file to enable integration login when MSSQL used. Then we only need to put username and password empty. Is that OK?

[road42]

I checked if you can simply leave user id= and password= empty in the connection string.

engine, err = xorm.NewEngine("mssql", "server=<server>;database=<database>;user id=;password=")

It still works.
Like the documentations of go-mssqldb says:

On Windows, if user id is empty or missing Single-Sign-On is used.

I think a simple switch in the config file or just letting me pass no "user id" and/or "password" should to it.

[lunny]

@road42 could you send a PR for that?

[road42]

I'd like to, but I am no go developer this is just a feature-request :)

[cboehme]

I just set up Gitea 1.22.0 with MSSQL and Windows Integrated Authentication without problems. My database configuration is

[database]
DB_TYPE = mssql
HOST = dbhost.example.com:1433
NAME = gitea
USER =
PASSWD =
SCHEMA =
SSL_MODE = disable
CHARSET = utf8
LOG_SQL = false

It works like a charm. I think this issue can be closed as the feature is working.