Access Denied Error

I'm running SQL Server 2000 on a Windows Server 2003 machine. We have sp3 o
n
SQL. I am getting an error when I query against my views. The views use a
linked server that grabs data from FoxPro dbf files located on the same
machine.
In resolving a memory issue, we disabled the 'Allow InProcess' setting on
the OLE DB for ODBC Drivers provider. I don't think this has anything to do
w/ the access denied error because I enabled 'Allow InProcess' to test it an
d
then disabled again when I got the error.
The error message is:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
returned 0x80070005: Access denied.].
The account is a domain account that is part of the administrators group on
the server. It is also a System Administrator role in SQL.
Any help is appreciated. Thanks, PhilPhil,
The obvious things to check are that the account running SQL Server
(which is likely not your Windows account) has the proper permissions
on the files in question, and that you are referring to them in a way that
is correct for that account (not using a drive letter defined at logon for
your own Windows account, for example)
Next, you could try a different way of accessing the linked server data.
Try an alternative among the 4-part name lnksrv.database.owner.table,
OpenQuery, OpenRowSet, or OpenDataSource. If there is another
provider besides MSDASQL, try it.
Beyond that, unfortunately, most newsgroup threads with this question
end with
no resolution. :( My best guess is to try adding this registry key,
though I'm not
certain this works for an arbitrary provider.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Providers\MSDASQL\Disallow
AdhocAccess
Make this a REG_DWORD with value 0.
Here is a link to a few dozen newsgroup threads on this issue - maybe
you'll find something.
http://groups-beta.google.com/group...rface+sqlserver
If you resolve this problem, please let us know!
Steve Kass
Drew University
phil wrote:

>I'm running SQL Server 2000 on a Windows Server 2003 machine. We have sp3
on
>SQL. I am getting an error when I query against my views. The views use a
>linked server that grabs data from FoxPro dbf files located on the same
>machine.
>In resolving a memory issue, we disabled the 'Allow InProcess' setting on
>the OLE DB for ODBC Drivers provider. I don't think this has anything to d
o
>w/ the access denied error because I enabled 'Allow InProcess' to test it a
nd
>then disabled again when I got the error.
>The error message is:
>Server: Msg 7399, Level 16, State 1, Line 1
>OLE DB provider 'MSDASQL' reported an error. Access denied.
>OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
>returned 0x80070005: Access denied.].
>The account is a domain account that is part of the administrators group on
>the server. It is also a System Administrator role in SQL.
>Any help is appreciated. Thanks, Phil
>
>|||Well, I resolved this issue by selecting 'Allow InProcess' for the OLE DB
Provider for ODBC Drivers. However, I had deselected this setting to correc
t
a memory issue I am having. Now, I'm looking to resolve that a different
way. (argh.)
- Phil
"Steve Kass" wrote:

> Phil,
> The obvious things to check are that the account running SQL Server
> (which is likely not your Windows account) has the proper permissions
> on the files in question, and that you are referring to them in a way that
> is correct for that account (not using a drive letter defined at logon for
> your own Windows account, for example)
> Next, you could try a different way of accessing the linked server data.
> Try an alternative among the 4-part name lnksrv.database.owner.table,
> OpenQuery, OpenRowSet, or OpenDataSource. If there is another
> provider besides MSDASQL, try it.
> Beyond that, unfortunately, most newsgroup threads with this question
> end with
> no resolution. :( My best guess is to try adding this registry key,
> though I'm not
> certain this works for an arbitrary provider.
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Providers\MSDASQL\Disall
owAdhocAccess
> Make this a REG_DWORD with value 0.
> Here is a link to a few dozen newsgroup threads on this issue - maybe
> you'll find something.
> http://groups-beta.google.com/group...rface+sqlserver
> If you resolve this problem, please let us know!
> Steve Kass
> Drew University
> phil wrote:
>
>|||An alternative solution is to use the -g switch, which we may pursue. This
is explained in BOL. - Phil
"phil" wrote:

> Well, I resolved this issue by selecting 'Allow InProcess' for the OLE DB
> Provider for ODBC Drivers. However, I had deselected this setting to corr
ect
> a memory issue I am having. Now, I'm looking to resolve that a different
> way. (argh.)
> - Phil
>

Access denied

Hello,
I have been running SQLSvr 2000 (sp3) on Win2k+SP3.
I upgraded Windows 2000 with Win2k SP4 yesterday, and
reinstalled SQLSvr 2000 and SQL Svr SP3. After that, I
cannot start up the SQl Svr from my following program ( I
can manually start the SQlSvr from the SQLSvr manager).
mySQLSvr = SQLDMO.SQLServer
mySQLSvr.start ibConnect mszSvrName
I got an unknown error : -2147024891. I looked into this
error, it is
"[9/4/2003 3:03:10 PM] Returned err numer:-2147024891; err
desc:[SQL-DMO]Service Control Error: Access is denied."\
Would you like to tell me if Win 2k Sp4 has changed any
security stuff?
I really appreciate your help.
Many thanks
MarkHeard from our sysadmin that Win2k SP4 has problems on servers. Did not go
into detail, but he said for the workstations it's fine, but not for server
.....
"Mark" <luyuan_l@.telus.net> wrote in message
news:228c01c3732b$8739dc90$a501280a@.phx.gbl...
> Hello,
> I have been running SQLSvr 2000 (sp3) on Win2k+SP3.
> I upgraded Windows 2000 with Win2k SP4 yesterday, and
> reinstalled SQLSvr 2000 and SQL Svr SP3. After that, I
> cannot start up the SQl Svr from my following program ( I
> can manually start the SQlSvr from the SQLSvr manager).
> mySQLSvr = SQLDMO.SQLServer
> mySQLSvr.start ibConnect mszSvrName
> I got an unknown error : -2147024891. I looked into this
> error, it is
> "[9/4/2003 3:03:10 PM] Returned err numer:-2147024891; err
> desc:[SQL-DMO]Service Control Error: Access is denied."\
> Would you like to tell me if Win 2k Sp4 has changed any
> security stuff?
> I really appreciate your help.
> Many thanks
> Mark
>

Accesing data from many applications.

Hi, I have a server running sql server 2000 sp3. In this sql server we
use windows authentication to allow users to use the applications that
were developed in house.
At this moment we have some users that are using excel to get data from
databases but we dont want they do that, because we cant control the
data they are getting.
We want to restrict that all users can get data only using the
applications that are developed in house.
Do you know if in sql server I can restrict wich applications can be
connected to sql server and wich not'
I need to continue using windows authentication.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***On Feb 24, 11:28 am, MariaGuzman <mar...@.devdex.com> wrote:
> Hi, I have a server running sql server 2000 sp3. In this sql server we
> use windows authentication to allow users to use the applications that
> were developed in house.
> At this moment we have some users that are using excel to get data from
> databases but we don=B4t want they do that, because we can=B4t control the
> data they are getting.
> We want to restrict that all users can get data only using the
> applications that are developed in house.
> Do you know if in sql server I can restrict wich applications can be
> connected to sql server and wich not'
> I need to continue using windows authentication.
> Thanks a lot for your help.
> *** Sent via Developersdexhttp://www.codecomments.com***
If a user has permission to connect they can.
See SQL Server 2005 Books Online topic:
Application Roles|||MariaGuzman (marisa@.devdex.com) writes:
> Hi, I have a server running sql server 2000 sp3. In this sql server we
> use windows authentication to allow users to use the applications that
> were developed in house.
> At this moment we have some users that are using excel to get data from
> databases but we dont want they do that, because we cant control the
> data they are getting.
> We want to restrict that all users can get data only using the
> applications that are developed in house.
> Do you know if in sql server I can restrict wich applications can be
> connected to sql server and wich not'
I assume here that your applications are not using stored procedures,
but generate SQL statements in the client. Because if the applications
are using stored procedures (with no dynamic SQL in them), the answer
would be trivial. Just revoke all direct table access. It's sufficient
that users have EXEC permissions on the stored procedure.
If your applications are not using stored procedures, it's of course
a major task to rewrite them. Using application roles as Steve suggested
is a possible solution. It still requires the application to be rewritten,
because once the user has logged in, the application must issue
"sp_setapprole". Note also that if you schemes that requires you
to know the name of the current database user, this will break with
application role.
Note also that application role with two-tier solution is not real
security. The password for the application must be hidden somewhere,
but whereever you hide it, a skilled user will be able to find it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

About Wait Type

SQL Server 2k Enterprise sp3
Windows 2k Server
XeonX2*2 2GMhz£¬2G RAM£¬RAID5
After excuting 'dbcc sqlperf(WAITSTATS)',The WaitType,including
RESOURCE_QUEUE¡¢LATCH_EX¡¢CXPACKET¡¢PAGEIOLATCH_SH, often appears .
But CPU's Usage is less than 60% and RAM has 90M free.
http://sqldev.net/misc/waittypes.htm did not tell me how to optimizing SQL
server performance .
I don't know whether I should add the CPU ,or the RAM, or anything else.
I want the SQL Server to do its best.
ThanksAre you experiencing any kind of problems on your sql server that may need
more memory?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

about transactional replication

Hi,
I have created a transactional replication on SQL Server 2000 with SP3. Only
one database is being published with 70 tables.
But when it begins to replicate data the following errors occured
""The process could not bulk copy into table 'tablename' ""
Violation of PRIMARY KEY constraint 'tablename_PKEY'. Cannot insert
duplicate key in object 'tablename'.
This table has a PKEY and it's not identity and this table also related with
other tables
This question is asked before but i have still no idea to solve this problem
without deleting the relationships of that table.
Please tell the solution step by step,
Best regards...
its hard to say what is going on here. There are a couple of possibilities:
1) data already exists at the subscriber and you are not deleting it before
sending over a new snapshot.
2) you are replicating two or more articles to the same object on the
subscriber.
3) you are getting a different error message than the one posted.
Are you replicating the DRI of these tables, or are you replicating to a
database where the schema already exists? If so you will need to send a
pre-snapshot command which disables all DRI, and a post snapshot command
which re-enables it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ugur Ekinci" <ugur@.argeset.com> wrote in message
news:OUdrkjtKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have created a transactional replication on SQL Server 2000 with SP3.
Only
> one database is being published with 70 tables.
> But when it begins to replicate data the following errors occured
> ""The process could not bulk copy into table 'tablename' ""
> Violation of PRIMARY KEY constraint 'tablename_PKEY'. Cannot insert
> duplicate key in object 'tablename'.
> This table has a PKEY and it's not identity and this table also related
with
> other tables
> This question is asked before but i have still no idea to solve this
problem
> without deleting the relationships of that table.
> Please tell the solution step by step,
> Best regards...
>
|||Hi Mr. Hilary,
Yes data already exists at the subscriber, how can i provide to delete
record before replicate it ?
how can I disable DRI and reenable it after replicaiton finished ?
It would be great if you give an example,
Best regards...
"Hilary Cotter" <hilary.cotter@.gmail.com>, haber iletisinde unlar
yazd:ulKrEeuKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> its hard to say what is going on here. There are a couple of
> possibilities:
> 1) data already exists at the subscriber and you are not deleting it
> before
> sending over a new snapshot.
> 2) you are replicating two or more articles to the same object on the
> subscriber.
> 3) you are getting a different error message than the one posted.
> Are you replicating the DRI of these tables, or are you replicating to a
> database where the schema already exists? If so you will need to send a
> pre-snapshot command which disables all DRI, and a post snapshot command
> which re-enables it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Ugur Ekinci" <ugur@.argeset.com> wrote in message
> news:OUdrkjtKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> Only
> with
> problem
>
|||It's ok, thanks Mr. Hilary,
Since i checked the "delete data in the existing table..." from publication
properties , that error does not occur ,
But I need to know that can we disable DRI with SP_ commands and enable it
again ?
Best regards...
"Hilary Cotter" <hilary.cotter@.gmail.com>, haber iletisinde unlar
yazd:ulKrEeuKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> its hard to say what is going on here. There are a couple of
> possibilities:
> 1) data already exists at the subscriber and you are not deleting it
> before
> sending over a new snapshot.
> 2) you are replicating two or more articles to the same object on the
> subscriber.
> 3) you are getting a different error message than the one posted.
> Are you replicating the DRI of these tables, or are you replicating to a
> database where the schema already exists? If so you will need to send a
> pre-snapshot command which disables all DRI, and a post snapshot command
> which re-enables it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Ugur Ekinci" <ugur@.argeset.com> wrote in message
> news:OUdrkjtKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> Only
> with
> problem
>