Re: One Connection String for Multiple Users (SQL)

I'm glad you figured it out. Yes, the tip-off was the sp_spidebug call.

--
__________________________________________________________________________
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:4DBC6706-C3DE-456B-A6A7-53FC86CB11B2@xxxxxxxxxxxxxxxx
Oh Bill - 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. That's what happens when guys
like me don't read the whole book! ;)

"jp2msft" wrote:

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