|
|
You're in the
sql server odbc
group:odbc call to sql 7 fails
sql server odbc:
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.)
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 [quoted text, click to view] >-----Original Message-----
>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... >> 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.) >> >> > > >.
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. ******************************************************************* [quoted text, click to view] "Janet" <janetb@mtn.ncahec.org> wrote in message news:27f501c3e104$cfd509b0$a501280a@phx.gbl... > 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.) > >
Unfortunately, SQL runs in mixed mode when it SQL security is used. If
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. ******************************************************************* [quoted text, click to view] "Janet" <janetb@mtn.ncahec.org> wrote in message news:34f501c3e1d8$33367ba0$a601280a@phx.gbl... > 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 > > > >-----Original Message----- > >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... > >> 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.) > >> > >> > > > > > >. > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |