Re: OPENROWSET/OPENDATASOURCE, Security, UNC - Help Please

From: Hal Berenson (haroldb@truemountainconsulting.com)
Date: 02/22/03


From: "Hal Berenson" <haroldb@truemountainconsulting.com>
Date: Sat, 22 Feb 2003 10:56:16 -0800


Interesting. There does seem to be any clear documentation of the behavior
in this case. I can't say I know the answer, but let me take a stab at it.

The JET provider is being instantiated outside the SQL Server process. When
the caller is SA (or perhaps any other SQL login) then the new process is
created using SQL Server service account. This is actually quite dangerous
since it can give non-privileged users the ability to perform privileged
operations (including OS operations). This is why DisallowAdhocAccess is
now on by default. When the caller is an integrated security login then SQL
Server is probably running the JET provider in a process with the Windows
login of the caller rather than the SQL Server service account.

You could try using a Linked Server so you can be more explicit about the
login mapping. Another option, although of some risk to the integrity of
the server, is to specify AllowInProcess = 1 for the JET provider so that it
runs inside the SQL Server process.

Hopefully this helps.

--
Hal Berenson
True Mountain Consulting
"Mike Mortensen" <mmortensen@#N#O#S#P#A#Mresolutionhealth.com> wrote in
message news:Xns93276C3B2C058mi123456789900@216.168.3.30...
> Posted this in .programming, thought this might be a more appropriate
> newsgroup...
>
> Hi All!
>
>     I have a question about the security context of the Jet Provider
> when using OPENROWSET or OPENDATASOURCE.  Below is the setup of the
> environment:
>
> Server: Win2k Server SP2
> SQL: SQL 2000 SP2
> Query:
> SELECT *
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
>    'paradox 5.x;database=\\server\share\', table)
>
> Security on '\\server\share': Everyone has Full Control
>
> Here is the problem I am seeing.  When i log into the server using
> integrated security (the account is a domain admin) the query fails with
> Msg 7399 which reads:
>
> [OLE/DB provider returned message: '\\server\share\' is not a valid path.
> Make sure that the path name is spelled correctly and that you are
> connected to the server on which the file resides.]
>
> However, when i login to the SQL Server as SA, the query runs
> successfully.  The SQL Server service account is an administrator on both
> machines.
>
> I have auditing turned on on the remote server and when i use the SA
> account, the event viewer shows the SQL Server service account
> connecting.  When i use the integrated security account, i see the
> Anonymous account trying to login.  Why is that?  Why does it not use the
> SQL Server service account when i'm logged in as a windows user?  Setting
> up a SQL Proxy account didn't resolve the problem.  I cannot find any
> explanation of this behavior.  Please help!


Relevant Pages

  • Re: Change default windows login in Integrated Security
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... I've a connection to a database using integrated security in order to ... Windows Login, I want use another login (an Active Directory validated ... Security connection in Sql Server 2000 - 2005?? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: error in executing exec xp_cmdshell
    ... > admin group. ... Did you assign the SQL Server service account the advanced user rights ... The rights are needed so that SQL Server ...
    (microsoft.public.sqlserver.security)
  • Re: OPENROWSET/OPENDATASOURCE, Security, UNC - Help Please
    ... The JET provider is being instantiated outside the SQL Server process. ... When the caller is an integrated security login then SQL ... login of the caller rather than the SQL Server service account. ...
    (microsoft.public.sqlserver.security)
  • Re: AWE Memory
    ... Check Lock pages in memory list for that account or whatever SQL Server service account it. ... it's recommended using a domain account as SQL Server service if your SQL Server is in a domain environment. ...
    (microsoft.public.sqlserver.security)
  • Re: Backup to Network Drive
    ... The SQL Server service account needs to be a domain account, ... Mike Epprecht, Microsoft SQL Server MVP ... > Am trying to backup to a network drive as the other machine has loads of ...
    (microsoft.public.sqlserver.msde)