?? unable to access SQL Server w/ sa account unless user is Administrator on local machine

?? unable to access SQL Server w/ sa account unless user is Administrator on local machine

Post by Daniel Prat » Fri, 06 Sep 2002 21:59:58



    We've encountered a wierd security problem with our app and SQL Server.
We're doing more testing, but I'm hoping someone has a clue as to what is
going on here.

    We have a client machine running XP and our app. The app connects to SQL
Server on another machine using "sa" and a password. SQL Server is
configured to accept both types of authentication. If the logged-in domain
user belongs to the local Administrators group, our app connects fine. If
the user does not belong to the local Administrators group, the app fails
with the typical "access denied" error:

        Error (-2147467259) [DBNETLIB][ConnectionOpen (Connect()).]SQL
Server does not exist or access denied.

    What possible difference could membership in the local Administrators
group make to connecting to SQL Server with a SQL Server user account?

    Thanks for any help.

Regards,
Dan

 
 
 

?? unable to access SQL Server w/ sa account unless user is Administrator on local machine

Post by A. Tolga KILIN » Fri, 06 Sep 2002 22:40:13


I think it can be good if you give "deny" to SQL BUILTIN/Administrators
group and try again and understand if the app. really logins using sa or
not.
Secondly, control the parameters (authentication method...etc) in ODBC if
possible.
That much I can estimate...
Tolga


Quote:>     We've encountered a wierd security problem with our app and SQL
Server.
> We're doing more testing, but I'm hoping someone has a clue as to what is
> going on here.

>     We have a client machine running XP and our app. The app connects to
SQL
> Server on another machine using "sa" and a password. SQL Server is
> configured to accept both types of authentication. If the logged-in domain
> user belongs to the local Administrators group, our app connects fine. If
> the user does not belong to the local Administrators group, the app fails
> with the typical "access denied" error:

>         Error (-2147467259) [DBNETLIB][ConnectionOpen (Connect()).]SQL
> Server does not exist or access denied.

>     What possible difference could membership in the local Administrators
> group make to connecting to SQL Server with a SQL Server user account?

>     Thanks for any help.

> Regards,
> Dan


 
 
 

?? unable to access SQL Server w/ sa account unless user is Administrator on local machine

Post by Ron Talmag » Sat, 07 Sep 2002 14:00:59


Daniel,

Check to see what kind of authentication your SQL Server is set to. If it's
Windows only, and if your account belongs to the local admins group, the sa
login will succeed but you'll actually log in using a trusted connection.
Then when the account is removed from the local admins group, the login will
fail, because sa is a SQL login, not a Windows trusted login.

Ron
--
Ron Talmage
SQL Server MVP


Quote:>     We've encountered a wierd security problem with our app and SQL
Server.
> We're doing more testing, but I'm hoping someone has a clue as to what is
> going on here.

>     We have a client machine running XP and our app. The app connects to
SQL
> Server on another machine using "sa" and a password. SQL Server is
> configured to accept both types of authentication. If the logged-in domain
> user belongs to the local Administrators group, our app connects fine. If
> the user does not belong to the local Administrators group, the app fails
> with the typical "access denied" error:

>         Error (-2147467259) [DBNETLIB][ConnectionOpen (Connect()).]SQL
> Server does not exist or access denied.

>     What possible difference could membership in the local Administrators
> group make to connecting to SQL Server with a SQL Server user account?

>     Thanks for any help.

> Regards,
> Dan

 
 
 

?? unable to access SQL Server w/ sa account unless user is Administrator on local machine

Post by Daniel Prat » Sat, 07 Sep 2002 21:20:58


Hi Tolga, Ron,


Quote:> Daniel,

> Check to see what kind of authentication your SQL Server is set to. If
it's
> Windows only, and if your account belongs to the local admins group, the
sa
> login will succeed but you'll actually log in using a trusted connection.
> Then when the account is removed from the local admins group, the login
will
> fail, because sa is a SQL login, not a Windows trusted login.

    Thanks for your responses. I'm sure that SQL Server is configured to
accept SQL Server and Windows authentication. I suppose it's possible that
some wierd set of circumstances has the app trying to use a trusted
connection, so I will experiment with disabling the BUILTIN\Administrators
group.

Regards,
Dan

 
 
 

?? unable to access SQL Server w/ sa account unless user is Administrator on local machine

Post by Dan Guisinge » Sun, 08 Sep 2002 02:37:29


OMG, someone else has the same problem we ran into
yesterday.  Its not documented anywhere from what I can
see.

We wrote our app in VS.NET and its designed to use a
trusted connection to localhost, unless it can't find
one.  Then it asks for a database, and in our office we
can tell it to pickup our SQL (MSDE) server using SQL
authentication and the account SA.

However we installed the application yesterday on 2
laptops for a local basketball organization using it for
registration, and one computer was to host the db, so
they could use two machines for entry.

However it didnt work.  I could log into the server
laptop just fine using SA, as mixed mode was enabled.

However the remote machine, even though OSQL -L listed
the server, it could not connect, giving me the EXACT
same error message you are receiving.

So I'll repeat your cry for help........HELP! Please,
this is extreamly frusterating.

Thanks,

Dan Guisinger
Atacomm / Ataractic Corporation

Quote:>-----Original Message-----
>    We've encountered a wierd security problem with our
app and SQL Server.
>We're doing more testing, but I'm hoping someone has a
clue as to what is
>going on here.

>    We have a client machine running XP and our app. The
app connects to SQL
>Server on another machine using "sa" and a password. SQL
Server is
>configured to accept both types of authentication. If

the logged-in domain
Quote:>user belongs to the local Administrators group, our app
connects fine. If
>the user does not belong to the local Administrators

group, the app fails
Quote:>with the typical "access denied" error:

>        Error (-2147467259) [DBNETLIB][ConnectionOpen
(Connect()).]SQL
>Server does not exist or access denied.

>    What possible difference could membership in the

local Administrators
Quote:>group make to connecting to SQL Server with a SQL Server
user account?

>    Thanks for any help.

>Regards,
>Dan

>.

 
 
 

?? unable to access SQL Server w/ sa account unless user is Administrator on local machine

Post by Lan Lewis-Bevan [M » Thu, 19 Sep 2002 05:20:43


You may check if your app is using Named Pipes or TCP/IP to connect to SQL
server.   If you change to the other protocol, will it make any difference?

Regards,

Lan Lewis-Bevan
SQL Server Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 
 
 

1. Proxy SQL Server Agent Account for Non SA Users

Hi,

I am trying to create a Proxy SQL Server Agent windows
Account for Non SA Users to execute Jobs like Active X
Scripts, Command Prompt Utilities etc.

For this I have created a local windows login account in
the box.

The SQL Server agent account is a Domain Account.

I am getting the following error when I uncheck the Non-
Sysadmin Job Step Proxy Account Check Box to enable non sa
jobs in the SQL Server Agent => Properties => Job System.

" Unable to set the SQL Agent Proxy account because of the
reason listed below. Error Executing Extented stored
procedure: Specified user cannot login. "

This user is able to login to the box as a local user.

What can be the reason for this ?

Thanks

Vivek

2. mySQL and PHP newbie

3. user always logs into server with SA account

4. Guru level FP Challenge!

5. Installing SQL2000 SP1 kills sa and builtin\administrator accounts

6. Help: Problem registering a database with RMAN on 8.1.7

7. User can not access OLAP Cube after change the SQL Server Account

8. Delete with a inner JOIN statement

9. Start SQL Server from a user account (access denied)

10. Password recovery for user sa on SQL Server 4.0 and SQL Server 7.0

11. User can not access OLAP Cube after change the SQL Server Account

12. Destroy sa account on SQL Server

13. SQL Server "sa" Account