Re: Trouble with SQL Server
From: Gary Xu (SQLGuy_at_EarthLink.Net)
Date: 06/27/03
- Next message: Vikrant V Dalwale [MS]: "RE: sp_runwebtask remote access error"
- Previous message: Dan Guzman: "Re: use of application roles"
- In reply to: Giacomo: "Trouble with SQL Server"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Thu, 26 Jun 2003 20:28:30 -0700
"Giacomo" <00avoidspam@solsticepoint.com> wrote in message
news:OAGfd#kODHA.1216@TK2MSFTNGP11.phx.gbl...
> I'm using a UDL file type login (see listing below). Within the file
type,
> is a user name in this case "quotes". For testing purposes, the Password
> for the user is quotes as well. To enable a temporary user on the
system,
> I've created a power user named "quotes" with a login of "quotes" on the
> local system (not a network login name, but just a local "power user"
login
> on my local machine where I am running a copy of SQL Server) Trouble is
> mostly this method works fine. The udl passes the name and password to
the
> connection object, I create a pool of connections and the app works.
>
A local power user is a NT login, not SQL login. Assume that is the account
you longin to SQL, then your string should be "Integrated Security=SSPI"
instead of "User ID=quotes;Password=quotes". And you can only access SQL
from the local machine that hosts your SQL server.
Maybe you also have SQL account with same name and password ("quotes"). That
would explain if your application also works from other machines
> Sometimes it doesn't. When I recreate the database from a backup (using
> restore), the login ceases to function. My error is generally of the
> "trusted connection" nature. If I reboot and play with the options, it
> seems to start working again. This is not a problem here during
> development, but when I distribute the db, the udl will be created with
the
> user's login name. (It obviously won't work on a non NT system; we
require
> Windows 2K.) I won't be around to play with everyone's computer to make
> sure the database is up and running in the user's directory.
>
When you restore a DB, you need to run
Exec sp_Change_Users_Login AUTO_FIX, quotes
to fix the login (assuming that's SQL login you are using)
> If I'm having trouble making this work now, I know it will be trouble then
> as well. I'm at a loss as to how it works sometimes and then, after
> restoring the database, it stops working. Any ideas?
>
> For example, if I hard code an "Integrated Security=SSPI", can I get
around
> the user login problem? There will be nothing sensitive in the
distributed
> database. On our network database will be accessed from our servers, in
> this context the data will be sensitive, so I may be overly concerned
about
> the distribution sensitivity. I'd like to see it work on both sides of
the
> fence; on the network and with a less sensitive database using the same
> executable, off the network.
>
> Giacomo
>
> [oledb]
> ; Everything after this line is an OLE DB initstring
> Provider=SQLOLEDB.1;Password=quotes;Persist Security Info=True;User
> ID=quotes;Initial Catalog=MYDatabaseName;Data Source=MyMachineName
>
>
The best way to handle your situation might be:
1 Create a domain group
2 Add users' domain account to this group
3. Grant this group proper access to your DB
4 Use connection string like Provider=SQLOLEDB.1;Integrated
Security=SSPI;Initial Catalog=MYDatabaseName;Data Source=MyMachineName
Good luck,
Gary
- Next message: Vikrant V Dalwale [MS]: "RE: sp_runwebtask remote access error"
- Previous message: Dan Guzman: "Re: use of application roles"
- In reply to: Giacomo: "Trouble with SQL Server"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
