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
– Stephen Gilboy, Commented Apr 24, 2023 at 18:46
"Cannot open database \"DevTest\" requested by the login. The login failed.\r\nLogin failed for user 'SG\\PC55'." — Vadim Tomashevsky
– Vadim Tomashevsky, Commented Apr 24, 2023 at 18:56
see if you have windows authentification and sql authenticator activated — nbk
– nbk, Commented Apr 24, 2023 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: stackoverflow.com/questions/9312853/… you can impersonate the user in question in c# code and then connect with the above SSPI. But impersonation has own problems :( — siggemannen
– siggemannen, Commented Apr 24, 2023 at 19:07

abolfazl sadeghi · Accepted Answer · 2023-04-24 18:55:09Z

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, 2023 at 18:55

answered Apr 24, 2023 at 18:49

abolfazl sadeghi

2,4182 gold badges15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Vadim Tomashevsky Over a year ago

"Cannot open database \"DevTest\" requested by the login. The login failed.\r\nLogin failed for user 'SG\\PC55'."

abolfazl sadeghi Over a year ago

you can connect to sql in ssms?you test connection with udl file?

abolfazl sadeghi Over a year ago

your connection string is "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DevTest;Data Source=B-SG-SQ;TrustServerCertificate=true"?

Vadim Tomashevsky Over a year ago

yes it is, i can't connect with ssms, this user is only for an application

abolfazl sadeghi Over a year ago

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 "

|

Vadim Tomashevsky · Accepted Answer · 2023-04-27 14:03:20Z

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));

Collectives™ on Stack Overflow

c# connection string not working with user domian

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related