c# connection string not working with user domian

Question

For some reason this connection string is working with node js and this is an application user:

var http = require("http");

const sql = require("mssql");

const port =3333;

sql.connect('Server=B-SG-SQ;Database=DevTest;User Id=sg\\DevtestB;Password=DevtestB123;Encrypt=true;trustServerCertificate=true')
.then((conn) => {
  console.log(conn);
console.log("MSSQL: connected");

conn.query(`select * from samples`)
.then(data => console.log(data))
.then(() => conn.close())
}).catch(err => { console.log("err " + err) });

And I can see the list of objects from my table.

But when I try it with an ASP.NET Core 7 Web API:

public class DataContext
{
    private readonly DbSettings _dbSettings;

    public DataContext(IOptions<DbSettings> dbSettings)
    {
        _dbSettings = dbSettings.Value;
    }

    public IDbConnection CreateConnection()
    {
        var connectionString = "Server=B-SG-SQ;Database=DevTest;User Id=sg\\DevtestB;Password=DevtestB123;Encrypt=true;TrustServerCertificate=true";
        return new SqlConnection(connectionString);
    }
}

This is the result that I see:

Login failed for user 'sg\DevtestB'

Does adding `Integrated Security=SSPI` to the connection string work? — Stephen Gilboy, Apr 24 '23 at 18:46
"Cannot open database \"DevTest\" requested by the login. The login failed.\r\nLogin failed for user 'SG\\PC55'." — Vadim Tomashevsky, Apr 24 '23 at 18:56
see if you have windows authentification and sql authenticator activated — nbk, Apr 24 '23 at 19:00
node probably uses kerberos to be able to login with another user, but i don't think c# can do it, see also here: https://stackoverflow.com/questions/9312853/sql-connection-string-using-a-domain-user you can impersonate the user in question in c# code and then connect with the above SSPI. But impersonation has own problems :( — siggemannen, Apr 24 '23 at 19:07
There's a lot of c# sample code that allows logging in with another user and then do stuff. Check it out — siggemannen, Apr 25 '23 at 07:11

abolfazl sadeghi · Answer 1 · 2023-04-24T18:55:09.400

0

when you use user domain ,you didn't need to use password sample connection string:

Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ProductDB;Data Source=.

you can Test connection with udl file

Test Test connection:Test connection with udl

edited Apr 24 '23 at 18:55

answered Apr 24 '23 at 18:49

abolfazl sadeghi

2,277
2
12
20

"Cannot open database \"DevTest\" requested by the login. The login failed.\r\nLogin failed for user 'SG\\PC55'." – Vadim Tomashevsky Apr 24 '23 at 18:56
you can connect to sql in ssms?you test connection with udl file? – abolfazl sadeghi Apr 24 '23 at 18:57
your connection string is "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DevTest;Data Source=B-SG-SQ;TrustServerCertificate=true"? – abolfazl sadeghi Apr 24 '23 at 18:59
yes it is, i can't connect with ssms, this user is only for an application – Vadim Tomashevsky Apr 24 '23 at 19:05
you must can connect with ssms even only for an application. maybe user is sql user.please check this connection "Password=DevtestB123;Persist Security Info=True;User ID=DevtestB;Initial Catalog=DevTest;Data Source=B-SG-SQ;TrustServerCertificate=True " – abolfazl sadeghi Apr 24 '23 at 19:11
"Login failed for user 'DevtestB'." This is the result – Vadim Tomashevsky Apr 24 '23 at 19:27

score 0 · Answer 2 · answered Apr 27 '23 at 14:03

Impersonation Middleware in an Asp.Net Core Intranet app for Windows-Identity

and https://learn.microsoft.com/en-us/dotnet/api/system.security.principal.windowsidentity.runimpersonated?view=net-7.0

It worked. But i had to change the code in the middleware to:

var domainName = IPGlobalProperties.GetPGlobalProperties().DomainName;

const int LOGON32_PROVIDER_DEFAULT = 0;  
        //This parameter causes LogonUser to create a primary token.   
        const int LOGON32_LOGON_INTERACTIVE = 9;  

        // Call LogonUser to obtain a handle to an access token.   
        SafeAccessTokenHandle safeAccessTokenHandle;  
        bool returnValue = LogonUser(userName, domainName, password,  
            LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT,  
            out safeAccessTokenHandle);

 await WindowsIdentity.RunImpersonatedAsync(safeAccessTokenHandle, async () => await _next.Invoke(context));

c# connection string not working with user domian

2 Answers2