0

I have a connection string like the following:

"Data Source=db; Initial Catalog=t; Integrated Security=SSPI; User id=u; Password=p"

I'm specifying a specific user i want people to be using to access the database. However, when they run the function that calls this, users are reporting an error that their user doesn't have permission to connect to that database.

I've stepped through i'm able to log in, so for some reason either a) i'm using the credentials and other users are not, or more likely b) i have access to the database and other users are not.

I'm not sure why it's not picking up this connection string and using it.

SqlCommand cmd = new SqlCommand();
SqlDataReader reader;

cmd.CommandText = searchString();
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

reader = cmd.ExecuteReader();

sqlConnection1 is the above string with proper values inplace of placeholders. I'm specifically not doing anything fancy here to just debug this. Can provide more info if not enough/needed. But that's the majority of the issue.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lenigod
  • 153
  • 5
  • 17
  • 3
    You have set `Integrated Security = SSPI`, which means the security is going to use Windows authentication, not a sql login with the UN/PW you are passing. You are effectively overriding your login creds. – Jacob H Mar 16 '18 at 19:49
  • So in the case of setting sspi, it's ignoring the windows user credentials i'm providing it? – Lenigod Mar 16 '18 at 19:51
  • 1
    You cannot provide Windows user credentials in a connection string at all. Only SQL server logins. (And if you're asking for, capturing or storing Windows credentials in any way, you're probably setting up a security hazard.) – Jeroen Mostert Mar 16 '18 at 19:51
  • 1
    You will have to use some sort of Impersonation approach. Which is [quite a bit more difficult](https://stackoverflow.com/questions/125341/how-do-you-do-impersonation-in-net). Creating a SQL login is really the other option if that is available to you. – Jacob H Mar 16 '18 at 19:53
  • Creating a SQL login is definitely the approach i will take. thank you. – Lenigod Mar 16 '18 at 19:56
  • if you want to use a specific login, then remove the integrated security, and use the user and password string. If you need my advice, use the integrated security only, and from SSMS you can adjust the user permissions on the Windows account in which the application will be running under. This is much safer way. – iSR5 Mar 16 '18 at 19:58

1 Answers1

3

Remove --> Security=SSPI;

When using integerated security, the credentials are always ignored. You are able to lock in because your user credentials are valid and making the hop. Other user credentials are not validated and therefore they are seeing the error.

Ross Bush
  • 14,648
  • 2
  • 32
  • 55
  • 1
    I'm not sure this solves the problem, if OP is trying to pass windows credentials. – Jacob H Mar 16 '18 at 19:54
  • Looks like i had a poor understanding of what sspi was doing in relation to connecting to a database. This clarifies it. I was trying to set it up with windows credentials. I've switched to integrated security=false, and set up a proper DB user in place of what i was trying to accomplish – Lenigod Mar 16 '18 at 19:55