dsn sits in same file as the mdb on a server (on purpose
so it gets backed up regularly). The odbc file connects
to an SQL 7 database on an NT4 machine. All sql driver
versions are 2000.81.9042.00 The authentication is an
sql account - not windows.
Two people can open and connect fine. One gets a login
failure.
If we place the 'failed' user into the domain
administrator account and he logs in at another machine,
he connects fine. If he tries to login from his machine,
still fails.
Two questions:
Why does it fail in either scenario at his machine?
Using sql authentication, how can I let him connect
WITHOUT being a member of the admin group?
Many thanks for any help!
(P.S. Same account works fine through an asp page on the
web without the user having to be in the administrator's
group.)At some level, Named Pipes connections still do make NT calls to see if you
have Windows rights on the other machine.
My suggestion is to either share a network drive or find a network drive
that you don't mind this person connecting to. Make sure they can connect
to that shared drive. Then see how that affects the SQL connection attempt.
Unshare the drive. See what happens to the SQL connection attempt.
Suggestion #2 is to go into SQL Enterprise manager and add a Windows NT
authentication entry for his user account. Maybe that will help.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Janet" <janetb@.mtn.ncahec.org> wrote in message
news:27f501c3e104$cfd509b0$a501280a@.phx.gbl...
quote:|||Andy,
> Have 3 XP machines running Office XP Pro. The odbc file
> dsn sits in same file as the mdb on a server (on purpose
> so it gets backed up regularly). The odbc file connects
> to an SQL 7 database on an NT4 machine. All sql driver
> versions are 2000.81.9042.00 The authentication is an
> sql account - not windows.
> Two people can open and connect fine. One gets a login
> failure.
> If we place the 'failed' user into the domain
> administrator account and he logs in at another machine,
> he connects fine. If he tries to login from his machine,
> still fails.
> Two questions:
> Why does it fail in either scenario at his machine?
> Using sql authentication, how can I let him connect
> WITHOUT being a member of the admin group?
> Many thanks for any help!
> (P.S. Same account works fine through an asp page on the
> web without the user having to be in the administrator's
> group.)
>
Many thanks for the reply.
I know this isn't the popular thinking, but I had set up
sql to take sql authentication only for security
purposes. That way, if someone compromises the network
he/she couldn't by default get to sql data and;
conversly, if someone compromises sql he/she couldn't by
default get to network files.
But, the only way I could get this guy connected was to
use Windows Authentication on a group, put him in it, and
then everything worked fine. I think it's because our pc
machines are XP and the server is still NT/sql7. (The
servers will be updated to 2003 soon with Active
Directory, so maybe this will "all go away".) So, by
default and no matter what you specify, authentication is
by Windows, not sql.
Again, thanks for your reply. If I continue to have
problems after the upgrade, I will be sure to try your
suggestion.
Janet
quote:
>--Original Message--
>At some level, Named Pipes connections still do make NT
calls to see if you
quote:
>have Windows rights on the other machine.
>My suggestion is to either share a network drive or find
a network drive
quote:
>that you don't mind this person connecting to. Make
sure they can connect
quote:
>to that shared drive. Then see how that affects the SQL
connection attempt.
quote:
>Unshare the drive. See what happens to the SQL
connection attempt.
quote:
>Suggestion #2 is to go into SQL Enterprise manager and
add a Windows NT
quote:
>authentication entry for his user account. Maybe that
will help.
quote:
>--
> ****************************************
*****************
**********
quote:
>Andy S.
>MCSE NT/2000, MCDBA SQL 7/2000
>andymcdba1@.NOMORESPAM.yahoo.com
>Please remove NOMORESPAM before replying.
>Always keep your antivirus and Microsoft software
>up to date with the latest definitions and product
updates.
quote:
>Be suspicious of every email attachment, I will never
send
quote:
>or post anything other than the text of a http:// link
nor
quote:
>post the link directly to a file for downloading.
>This posting is provided "as is" with no warranties
>and confers no rights.
> ****************************************
*****************
**********
quote:|||Unfortunately, SQL runs in mixed mode when it SQL security is used. If
>"Janet" <janetb@.mtn.ncahec.org> wrote in message
>news:27f501c3e104$cfd509b0$a501280a@.phx.gbl...
file[QUOTE]
purpose[QUOTE]
connects[QUOTE]
machine,[QUOTE]
machine,[QUOTE]
the[QUOTE]
administrator's[QUOTE]
>
>.
>
someone compromises the administrative NT account, they'll get into Windows
and SQL. In that sense Windows NT authentication is secure.
To really secure your SQL server from a Windows perspective, always password
the SA account, consider restricting rights on xp_cmdshell (that procedure
in the master database grants DOS level access to administrators) and try
putting a proxy account for non-admin users for SQL Server agent.
The authentication models still get frustrating for me. I can't stand the
fact that when you restore a database to a separate server, the logins and
users can get out of sync requiring the procedure sp_change_users_login
auto_fix, user_id. (try restoring a database on a separate server), then
add a login with the user name of a user in the database. That is always a
minor annoyance.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Janet" <janetb@.mtn.ncahec.org> wrote in message
news:34f501c3e1d8$33367ba0$a601280a@.phx.gbl...[QUOTE]
> Andy,
> Many thanks for the reply.
> I know this isn't the popular thinking, but I had set up
> sql to take sql authentication only for security
> purposes. That way, if someone compromises the network
> he/she couldn't by default get to sql data and;
> conversly, if someone compromises sql he/she couldn't by
> default get to network files.
> But, the only way I could get this guy connected was to
> use Windows Authentication on a group, put him in it, and
> then everything worked fine. I think it's because our pc
> machines are XP and the server is still NT/sql7. (The
> servers will be updated to 2003 soon with Active
> Directory, so maybe this will "all go away".) So, by
> default and no matter what you specify, authentication is
> by Windows, not sql.
> Again, thanks for your reply. If I continue to have
> problems after the upgrade, I will be sure to try your
> suggestion.
> Janet
>
> calls to see if you
> a network drive
> sure they can connect
> connection attempt.
> connection attempt.
> add a Windows NT
> will help.
> **********
> updates.
> send
> nor
> **********
> file
> purpose
> connects
> machine,
> machine,
> the
> administrator's
