One Connection String for Multiple Users (C#)

[=?Utf-8?B?anAybXNmdA==?=]

We have one connection string to our SQL Server 2000:

"Data Source=WORKAPP; Initial Catalog=Parts_D ata;Integrated
Security=SSPI;U ser ID=public;Passw ord=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]

Re: One Connection String for Multiple Users (C#)



jp2msft wrote:

We have one connection string to our SQL Server 2000:
>
"Data Source=WORKAPP; Initial Catalog=Parts_D ata;Integrated
Security=SSPI;U ser ID=public;Passw ord=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]

Re: One Connection String for Multiple Users (C#)

My guess:

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

Security=SSPI;

(and)
User ID=public;Passw ord=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

"jp2msft" <jp2msft@discus sions.microsoft .comwrote in message
news:ABD9997F-1A2C-4A6C-96C2-A56543F6F0C5@mi crosoft.com...

We have one connection string to our SQL Server 2000:
>
"Data Source=WORKAPP; Initial Catalog=Parts_D ata;Integrated
Security=SSPI;U ser ID=public;Passw ord=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.

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Re: One Connection String for Multiple Users (C#)

sloan wrote:

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

>Security=SSP I;

(and)
User ID=public;Passw ord=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_Connect ion=False
in the connection string.

Arne

[sloan]

Re: One Connection String for Multiple Users (C#)

Nice extra tidbit of info!


"Arne Vajhøj" <arne@vajhoej.d kwrote in message
news:491363ea$0 $90267$14726298 @news.sunsite.d k...

sloan wrote:

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

>>Security=SSPI ;

>(and)
>User ID=public;Passw ord=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_Connect ion=False
in the connection string.
>
Arne
>

[=?Utf-8?B?anAybXNmdA==?=]

Re: One Connection String for Multiple Users (C#)

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_PART S;Integrated Security=False; User
Name=codeonly;P assword=codeonl y"

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.Sql Client.SqlConne ction.OnError(S qlException exception,
Boolean breakConnection )"

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

[=?Utf-8?B?anAybXNmdA==?=]

Re: One Connection String for Multiple Users (C#)

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.

"jp2msft" wrote:

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_PART S;Integrated Security=False; User
Name=codeonly;P assword=codeonl y"
>
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.Sql Client.SqlConne ction.OnError(S qlException exception,
Boolean breakConnection )"
>
How do I fix this? Why is a Restricted user allowed access using this string
whereas anything above is not?

[sloan]

Re: One Connection String for Multiple Users (C#)

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.



"jp2msft" <jp2msft@discus sions.microsoft .comwrote in message
news:33567F38-2C4C-4093-8BD4-74FEADB76642@mi crosoft.com...

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.
>
"jp2msft" wrote:
>

>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_PART S;Integrated
>Security=False ;User
>Name=codeonly; Password=codeon ly"
>>
>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.Sql Client.SqlConne ction.OnError(S qlException exception,
>Boolean breakConnection )"
>>
>How do I fix this? Why is a Restricted user allowed access using this
>string
>whereas anything above is not?