Re: One Connection String for Multiple Users (SQL)

Tech-Archive recommends: Speed Up your PC by fixing your registry

Ok, we are getting closer.

I am using option "a", as you describe below: we created an application
specific login ID and password (called codeonly/codeonly) and use the
following connection string:

"Data Source=WORKAPP;Initial Catalog=CompParts;Integrated
Security=False;User ID=codeonly;Password=codeonly"

Out on our factory production floor, all of the computers that are logged in
using an account with a Group Membership setting 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)"

The restricted users do not see this. Any ideas?

"William (Bill) Vaughn" wrote:

"Now everyone who does not have authorization can connect."
I hope your description of who can connect is backwards... ;)

So, (as you'll read in Chapter 9 -- and be sure to download the updated version from the Premium content area), you have several options. These include:
a.. Use an application-specific login ID and password. This uses the same connection string for all instances of the application. It assumes that if the user has access to the application, they have access to the database it accesses. Frankly, I think this can be a valid assumption, but it might make some uneasy as if someone leaves the application running or the workstation unlocked, someone else can use it. Of course, most application security approaches have the same problem. Because of this, I install watchdog timers to timeout the application if there is no activity for N seconds/minutes.
b.. Use Integrated Security=SSPI. In this case (again) if the user has rights to the system, the application can be run but in this case, the domain authenticated user must be recognized by a specific Login ID in the database that has been granted rights to the database and the specific objects (tables, stored procedures, views etc.) that the application needs to use. In most cases, the DBA will create a Role that contains the names of authorized users. While each user will not be granted rights to the database objects, you can grant rights to the Role. For example, you can have a "Application47Role" created. Next, you add just those domain Windows IDs to the Role. This approach means more administrative work in that as people leave or their duties change you'll need to add them to the Role (or someone will).
Either approach can permit you to use the same ConnectionString for all of the applications.

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________



"jp2msft" <jp2msft@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:B151FD19-7226-47B4-97E1-BF60760CB157@xxxxxxxxxxxxxxxx
Ok, I've fixed this by setting Integrated Security to False.


However, everyone who was authorized before is unable to connect to the
server using this same connection string.

Is there a way I can just have a single connection string to service
everyone in the plant?

FYI: I have your book open and I'm hunting the answer, but chapter 9 is
almost 100 pages. The company wants this done, and if they see me spending a
couple of days reading a book, they'll say, "Let's get rid of this guy!" If I
can get a quick answer, that would certainly be better.

"William (Bill) Vaughn" wrote:

First, you're using a belt and suspenders. When you specify a trusted
connection (Integrated Security=SSPI) the user's credentials are used
(instead of the UID and Password). I expect that the individuals with
limited rights are not granted sufficient rights to the database or to the
login account on the SQL Server. See Chapter 9 of my book.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________



"jp2msft" <jp2msft@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2BD86AA9-4C9E-485A-8224-E756EAB7D4BC@xxxxxxxxxxxxxxxx
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
.

Relevant Pages