One Connection String for Multiple Users (C#)

[jp2msft]

We have one connection string to our SQL Server 2000:

"Data Source=WORKAPP;Initial Catalog=Parts_Data;Integrated
Security=SSPI;User ID=public;Password=public";

Everyone can connect to the database in our plant except for the Restricted
Users.

Why can't the Restricted Users access this? How can we get around it?

I can access the data, but I am set up as an Administrator.

Our managers can access the data, but they are set up as Power Users.

Most machines are Windows XP, but some use Citrix Servers and a few are
running Windows Vista. The OS does not seem to make any difference.

We do not want to grand Power User status to everyone, and we should not
need to with the correct connection string.

 

[Ashutosh Bhawasinka]

We have one connection string to our SQL Server 2000:

"Data Source=WORKAPP;Initial Catalog=Parts_Data;Integrated
Security=SSPI;User ID=public;Password=public";

Everyone can connect to the database in our plant except for the Restricted
Users.

Why can't the Restricted Users access this? How can we get around it?

I can access the data, but I am set up as an Administrator.

Our managers can access the data, but they are set up as Power Users.

Most machines are Windows XP, but some use Citrix Servers and a few are
running Windows Vista. The OS does not seem to make any difference.

We do not want to grand Power User status to everyone, and we should not
need to with the correct connection string.

Just check if other users (which are not member of Administrators or
Power Users group) can access any other resources on the system on which
your SQL server is running.

 

[sloan]

My guess:

You have integrated security in there AND sql authentication credentials.
Integrated

Security=SSPI;

(and)
User ID=public;Password=public

Pick **one or the other** for clarity.

http://www.connectionstrings.com/?carrier=sqlserver

I think integrated will override sql authentication......<<< but you
shouldn't be testing this theory.
Get a "correct" connection string in there.

http://www.connectionstrings.com/?carrier=sqlserver

 

[Arne Vajhøj]

You have integrated security in there AND sql authentication credentials.
Integrated
(and)
User ID=public;Password=public

Pick **one or the other** for clarity.

http://www.connectionstrings.com/?carrier=sqlserver

I think integrated will override sql authentication......<<< but you
shouldn't be testing this theory.
Get a "correct" connection string in there.

http://www.connectionstrings.com/?carrier=sqlserver

It is allowed to be more explicit with:
Trusted_Connection=False
in the connection string.

Arne

 

[sloan]

Nice extra tidbit of info!

It is allowed to be more explicit with:
Trusted_Connection=False
in the connection string.

Arne

 

[jp2msft]

We created an account on our SQL Server 2000 (w/SP4 I believe) called
"codeonly" with the password "codeonly" and it is set up in the SQL Server
Group "WORKAPP (Windows NT)" with the Database Roles of public (I think this
is a company account), db_datareader and db_datawriter.

This is the connection string I am now using:

"Data Source=WORKAPP;Initial Catalog=CO_PARTS;Integrated Security=False;User
Name=codeonly;Password=codeonly"

Out on our factory production floor, all of the computers that are logged in
using an account with a Group Membership of "Restricted user (Users Group)"
can access the data; however, anyone logged in with "Standard user (Power
Users Group)" or "Other: Administrator" is refused connection.

The error message is:

"EXECUTE permission denied on object 'sp_sdidebug', database 'master', owner
'dbo'."

The first line of the StackTrace says:

" at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)"

How do I fix this? Why is a Restricted user allowed access using this string
whereas anything above is not?

 

[jp2msft]

Oh - I just found it!

In the project properties, I had enabled SQL Server debugging, but the
global User ID/Password did not have authorization to do this!

Solution: Uncheck "Enable SQL Server debugging" or set Integrated Security
to SSPI (True).

I wanted to post the results. I hope someone that needs help is able to pull
this post one day.

I hope I didn't take up too much of your time.

 

[sloan]

Thanks for posting the ultimate fix you found.

Nothing drive me nuts worse than an old old thread with the answer (almost)
there, and then the last post says
"I'll give it a try and let you know".

and ...there is no "letting you know" followup.