Hi!
I have stored procedure that executes BULK INSERT command inside.
Recently I migrated my sql 7 database to sql 2000 SP2.
Now when running this stored procedure from both application and Query
Analyzer I'm getting this message:
Server: Msg 4861, Level 16, State 1, Line 1 Could not bulk insert
because file '\\HSCDRMAN01\CDR\BCPFiles\BCP_cdr_05301
999.close' could
not be opened. Operating system error code 5(Access is denied.).
I found article on Microsoft web site that this is a known bug and it
only applies to SQL Server 7 (BUG #: 56167 (SQLBUG_70) )
Why am I getting this error in SQL Server 2000?
Should I apply the latest SP to SQL 2000 to fix this security issue?
Thank you,
YelenaI checked all permissions.
SQL Server service is running under domain account and this user has
access to the share where the file is located.
Also when executing BULK INSERT with master..xp_cmdshell I'm logged in
as sa.
I can't figure out why I'm getting Access Denied error.
Please help.
enaglozman@.yahoo.com (Lena) wrote in message news:<fc1e8071.0403160709.2dfad366@.posting.goo
gle.com>...
> Hi!
> I have stored procedure that executes BULK INSERT command inside.
> Recently I migrated my sql 7 database to sql 2000 SP2.
> Now when running this stored procedure from both application and Query
> Analyzer I'm getting this message:
> Server: Msg 4861, Level 16, State 1, Line 1 Could not bulk insert
> because file '\\HSCDRMAN01\CDR\BCPFiles\BCP_cdr_05301
999.close' could
> not be opened. Operating system error code 5(Access is denied.).
> I found article on Microsoft web site that this is a known bug and it
> only applies to SQL Server 7 (BUG #: 56167 (SQLBUG_70) )
> Why am I getting this error in SQL Server 2000?
> Should I apply the latest SP to SQL 2000 to fix this security issue?
> Thank you,
> Yelena|||Does it fail if you are logged on as the account running MSSQLServer
service? Also, that account will need read permissions to the share/file
that you reference:
'\\HSCDRMAN01\CDR\BCPFiles\BCP_cdr_05301
999.close'
Steve
"Lena" <lenaglozman@.yahoo.com> wrote in message
news:fc1e8071.0403230855.57f26b9a@.posting.google.com...
> I checked all permissions.
> SQL Server service is running under domain account and this user has
> access to the share where the file is located.
> Also when executing BULK INSERT with master..xp_cmdshell I'm logged in
> as sa.
> I can't figure out why I'm getting Access Denied error.
> Please help.
> enaglozman@.yahoo.com (Lena) wrote in message
news:<fc1e8071.0403160709.2dfad366@.posting.google.com>...sql
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Thursday, March 29, 2012
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
>
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 after installing XP-SP2
Hi,
we have an app running in a network where the MSDE database is running on
win XP Pro PC. This has been running perfect since january but after
installing XP SP2 we get "SQL server does not exist or access denied" when
trying to run the app from a client. The app runs fine when started on the
server.
See my reply to "MSDE over the network". You may need to apply SP3 to MSDE.
Also, check the Windows Firewall in control panel and make sure that there
is an exception for sqlservr.exe or adjust the firewall accordingly to allow
incoming connections and broadcast packets from MSDE.
Jim
"Olav" <Olav@.discussions.microsoft.com> wrote in message
news:CB7DBF9C-07DA-4EF0-A822-C7F1EE5617D4@.microsoft.com...
> Hi,
> we have an app running in a network where the MSDE database is running on
> win XP Pro PC. This has been running perfect since january but after
> installing XP SP2 we get "SQL server does not exist or access denied" when
> trying to run the app from a client. The app runs fine when started on the
> server.
|||Thanks Jim,
making an exception for sqlserver.exe solved it!
"Jim Young" wrote:
> See my reply to "MSDE over the network". You may need to apply SP3 to MSDE.
> Also, check the Windows Firewall in control panel and make sure that there
> is an exception for sqlservr.exe or adjust the firewall accordingly to allow
> incoming connections and broadcast packets from MSDE.
> Jim
>
> "Olav" <Olav@.discussions.microsoft.com> wrote in message
> news:CB7DBF9C-07DA-4EF0-A822-C7F1EE5617D4@.microsoft.com...
>
>
we have an app running in a network where the MSDE database is running on
win XP Pro PC. This has been running perfect since january but after
installing XP SP2 we get "SQL server does not exist or access denied" when
trying to run the app from a client. The app runs fine when started on the
server.
See my reply to "MSDE over the network". You may need to apply SP3 to MSDE.
Also, check the Windows Firewall in control panel and make sure that there
is an exception for sqlservr.exe or adjust the firewall accordingly to allow
incoming connections and broadcast packets from MSDE.
Jim
"Olav" <Olav@.discussions.microsoft.com> wrote in message
news:CB7DBF9C-07DA-4EF0-A822-C7F1EE5617D4@.microsoft.com...
> Hi,
> we have an app running in a network where the MSDE database is running on
> win XP Pro PC. This has been running perfect since january but after
> installing XP SP2 we get "SQL server does not exist or access denied" when
> trying to run the app from a client. The app runs fine when started on the
> server.
|||Thanks Jim,
making an exception for sqlserver.exe solved it!
"Jim Young" wrote:
> See my reply to "MSDE over the network". You may need to apply SP3 to MSDE.
> Also, check the Windows Firewall in control panel and make sure that there
> is an exception for sqlservr.exe or adjust the firewall accordingly to allow
> incoming connections and broadcast packets from MSDE.
> Jim
>
> "Olav" <Olav@.discussions.microsoft.com> wrote in message
> news:CB7DBF9C-07DA-4EF0-A822-C7F1EE5617D4@.microsoft.com...
>
>
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
>
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
>
Tuesday, March 27, 2012
Access DB Security Problem - Works on localhost
I have RS 2005 running on a 2003 server.
This report access an Access database located on a network fileshare, which
is a different server than where RS is running. Using Windows Integrated
Security, the report runs fine on the RS server (logged in as myself), but
will not run when I try to access the report from my machine (again, logged
in as myself). The error I get on my machine is:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'CAS'. (rsErrorOpeningConnection)
The Microsoft Jet database engine cannot open the file [SERVER]. It is
already opened exclusively by another user, or you need permission to view
its data.
Again, I only get this when accessing from my machine. The report works
fine from the RS host server. The access database is located on a different
machine. The user is the same in both instances, so something else is going
on. Please help.Under the credentials for the data source on the server try putting in a
user and password (store the password) and see if that works. I think what
is hitting you is the double hop issue.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"honus" <honus@.discussions.microsoft.com> wrote in message
news:9B43E798-636B-43BF-9867-9209E5E48B3D@.microsoft.com...
>I have RS 2005 running on a 2003 server.
> This report access an Access database located on a network fileshare,
> which
> is a different server than where RS is running. Using Windows Integrated
> Security, the report runs fine on the RS server (logged in as myself), but
> will not run when I try to access the report from my machine (again,
> logged
> in as myself). The error I get on my machine is:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'CAS'.
> (rsErrorOpeningConnection)
> The Microsoft Jet database engine cannot open the file [SERVER]. It is
> already opened exclusively by another user, or you need permission to view
> its data.
> Again, I only get this when accessing from my machine. The report works
> fine from the RS host server. The access database is located on a
> different
> machine. The user is the same in both instances, so something else is
> going
> on. Please help.
This report access an Access database located on a network fileshare, which
is a different server than where RS is running. Using Windows Integrated
Security, the report runs fine on the RS server (logged in as myself), but
will not run when I try to access the report from my machine (again, logged
in as myself). The error I get on my machine is:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'CAS'. (rsErrorOpeningConnection)
The Microsoft Jet database engine cannot open the file [SERVER]. It is
already opened exclusively by another user, or you need permission to view
its data.
Again, I only get this when accessing from my machine. The report works
fine from the RS host server. The access database is located on a different
machine. The user is the same in both instances, so something else is going
on. Please help.Under the credentials for the data source on the server try putting in a
user and password (store the password) and see if that works. I think what
is hitting you is the double hop issue.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"honus" <honus@.discussions.microsoft.com> wrote in message
news:9B43E798-636B-43BF-9867-9209E5E48B3D@.microsoft.com...
>I have RS 2005 running on a 2003 server.
> This report access an Access database located on a network fileshare,
> which
> is a different server than where RS is running. Using Windows Integrated
> Security, the report runs fine on the RS server (logged in as myself), but
> will not run when I try to access the report from my machine (again,
> logged
> in as myself). The error I get on my machine is:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'CAS'.
> (rsErrorOpeningConnection)
> The Microsoft Jet database engine cannot open the file [SERVER]. It is
> already opened exclusively by another user, or you need permission to view
> its data.
> Again, I only get this when accessing from my machine. The report works
> fine from the RS host server. The access database is located on a
> different
> machine. The user is the same in both instances, so something else is
> going
> on. Please help.
Access database connection
I am running Visual Studios 2005 enterprise edition and I am creating a mobile application to keep track of some receipts. The problem I'm currently having is that even though I can connect to the MS Access Database with Server Explorer that I'll be aquiring data from; Everytime I attempt to use the Data Configuration Wizard to add a new data source that namely the database I get this error message:
The new connection uses a data provider that is not supported. (end of message)
There is no username or password necessary to access this database and it is an Access 2003 database.
Any help would be greatly appreciated.
Just to make sure...
You did change the Data Source to:
Microsoft Access Database File (OLE DB)
Which will use the following .NET Data Provider:
.NET Framework Data Provider for OLE DB
I assume you did in order to select the database, but I just want to make sure.
And when you click the button "Test Connection", it gives you that error?
Regards,
Dave
|||
> mobile application to keep track of some receipts. The problem I'm
> currently having is that even though I can connect to the MS Access
> Database with Server Explorer that I'll be aquiring data from; Everytime
> I attempt to use the Data Configuration Wizard to add a new data source
> that namely the database I get this error message: >
> The new connection uses a data provider that is not supported. (end of
> message) >
> There is no username or password necessary to access this database and
> it is an Access 2003 database. >
> Any help would be greatly appreciated. >
>|||
I have no idea sir, I started the project as a smart device app, and in all the walkthroughs I have viewed nothing has been said about the Jet database engine being incompatible with mobile devices. If this is the case I'd like to know because this project is currently dependent on VS being able to connect with our Access database.
I'm not sure of the limitations, you're best asking at the SQL Mobile Forum.
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=152&SiteID=1
Am moving this thread to there.
Here's some info on SQL Server 2005 Mobile Edition
http://msdn.microsoft.com/sql/mobile/default.aspx
Alex.
|||The JET database engine runs only on desktop and server versions of Windows. It's certainly possible to develop a mobile web app that talks to a JET database on the server, but if you need a local data store on the mobile device that will remain available when the device is not connected to the network, then you will not be able to use JET. Your alternatives - as I understand it - include SQL Server CE and XML. There is a database engine on the Pocket PC called 'Pocket Access' but it has very little to do with the desktop product, and there is no built-in support for 'Pocket Access' in the .NET framework. There is, however, a third-party component you could look at. There's an article on this at the URL below, and there's a link from the article to the vendor of the third-party component. Note that I haven't personally used this, and I don't know what plans, if any, Microsoft has to continue to support 'Pocket Access'. So this isn't a recommendation, just something that may be worth further investigation. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnroad/html/road10222003.asp There's an interesting article on 'Data Access Strategies for the Microsoft ..NET Compact Framework' at the following URL. Although the article dates back to the .NET 1.0 timeframe, I believe it is still relevant. http://msdn.microsoft.com/mobility/understanding/articles/default.aspx?pull=/library/en-us/dnnetcomp/html/dataaccessstrategy.asp -- Brendan Reynolds Access MVP> This post has been edited either by the author or a moderator in the
> Microsoft Forums: http://forums.microsoft.com
> To: Mr. Reynolds
> I have no idea sir, I started the project as a smart device app, and in
> all the walkthroughs I have viewed nothing has been said about the Jet
> database engine being incompatible with mobile devices. If this is the
> case I'd like to know because this project is currently dependent on VS
> being able to connect with our Access database. > > > >
>|||Thank you for the information. Unfortunately I have a follow up question. If I were to make an intermediate SQL database to function as the go between for a SQL Mobile database and a Microsoft access database, is such a solution even possible? Our organization recently purchased VS 2005 on the assumption that it would be easy to use the .NET framework to develop a mobile app that would easily transfer data to an access database. Now with this new information its quite possible the data will go SQL Mobile -> SQL Database -> MS Access database.
Footnote:
The SQL Database would just function as a temporary store to check data generated from receipts made in the mobile database. Once the checks are done the corrected data would be passed to the MS Access database. Then the SQL database would wipe itself clean.
|||
Yes, but it makes no sense. To accomplish that you would have to purchase SQL 2000/2005 and pay CALs per device (or per CPU license). If that’s acceptable, simply get rid of Access and use much more powerful SQL you've paid for.
Alternative solution is to use Web Service to talk to Access database. Create couple WEB methods to talk to access and call them from device. Something like this should do:
DataSet GetAccessData(String command, …)
{
// Connect to Access DB here, execute command and populate data set with returned data.
return dataSet;
}
Bool UpdateAccessData(DataSet dataSet, … )
{
// Connect to Access DB here and update DB with data from Data Set.
return success;
}
|||To: IlyaThe enterprise edition that we purchased of VBS 2005 came with SQL server 2005 and I just installed it upon learning that I can't communicate directly with the Access database. I'm not sure if we still have to pay the CALS per device though.
If I used these web service methods where would I write them? In the upload and download portion of the code for the mobile app?
|||
I believe it comes with 5 CALs (to be verified).
These WS methods would run on desktop via IIS which would host WS. To create them, please click ‘Create/Web Site’ and choose "ASP.Net Web Service". You'll get project skeleton, simply add code you need and run it. Decorate added methods with WebMethod attribute. This is desktop project, so you could use anything desktop has to offer.
Calling WS from device pretty much looks like a normal function call. All necessary code would be created for you automatically as soon as you add Web Reference to your project.
|||i want to connecting Access database on PDA ?i am setting Access database on PDA..
can i do ?
and how i doing?|||How would one connect to an access database inside the web method?
|||
while the .NET Compact Framework has no APIs to leverage Access databases, there is an open source set of APIs that support Access at www.opennetcf.org
there are also some commercial libraries available - use Google Advanced Groups search on microsoft.public.dotnet.framework.compactframework and look for "Access Database"
Darren
Sunday, March 25, 2012
Access database connection
I am running Visual Studios 2005 enterprise edition and I am creating a mobile application to keep track of some receipts. The problem I'm currently having is that even though I can connect to the MS Access Database with Server Explorer that I'll be aquiring data from; Everytime I attempt to use the Data Configuration Wizard to add a new data source that namely the database I get this error message:
The new connection uses a data provider that is not supported. (end of message)
There is no username or password necessary to access this database and it is an Access 2003 database.
Any help would be greatly appreciated.
Just to make sure...
You did change the Data Source to:
Microsoft Access Database File (OLE DB)
Which will use the following .NET Data Provider:
.NET Framework Data Provider for OLE DB
I assume you did in order to select the database, but I just want to make sure.
And when you click the button "Test Connection", it gives you that error?
Regards,
Dave
|||
> mobile application to keep track of some receipts. The problem I'm
> currently having is that even though I can connect to the MS Access
> Database with Server Explorer that I'll be aquiring data from; Everytime
> I attempt to use the Data Configuration Wizard to add a new data source
> that namely the database I get this error message: >
> The new connection uses a data provider that is not supported. (end of
> message) >
> There is no username or password necessary to access this database and
> it is an Access 2003 database. >
> Any help would be greatly appreciated. >
>|||
I have no idea sir, I started the project as a smart device app, and in all the walkthroughs I have viewed nothing has been said about the Jet database engine being incompatible with mobile devices. If this is the case I'd like to know because this project is currently dependent on VS being able to connect with our Access database.
I'm not sure of the limitations, you're best asking at the SQL Mobile Forum.
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=152&SiteID=1
Am moving this thread to there.
Here's some info on SQL Server 2005 Mobile Edition
http://msdn.microsoft.com/sql/mobile/default.aspx
Alex.
|||The JET database engine runs only on desktop and server versions of Windows. It's certainly possible to develop a mobile web app that talks to a JET database on the server, but if you need a local data store on the mobile device that will remain available when the device is not connected to the network, then you will not be able to use JET. Your alternatives - as I understand it - include SQL Server CE and XML. There is a database engine on the Pocket PC called 'Pocket Access' but it has very little to do with the desktop product, and there is no built-in support for 'Pocket Access' in the .NET framework. There is, however, a third-party component you could look at. There's an article on this at the URL below, and there's a link from the article to the vendor of the third-party component. Note that I haven't personally used this, and I don't know what plans, if any, Microsoft has to continue to support 'Pocket Access'. So this isn't a recommendation, just something that may be worth further investigation. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnroad/html/road10222003.asp There's an interesting article on 'Data Access Strategies for the Microsoft ..NET Compact Framework' at the following URL. Although the article dates back to the .NET 1.0 timeframe, I believe it is still relevant. http://msdn.microsoft.com/mobility/understanding/articles/default.aspx?pull=/library/en-us/dnnetcomp/html/dataaccessstrategy.asp -- Brendan Reynolds Access MVP> This post has been edited either by the author or a moderator in the
> Microsoft Forums: http://forums.microsoft.com
> To: Mr. Reynolds
> I have no idea sir, I started the project as a smart device app, and in
> all the walkthroughs I have viewed nothing has been said about the Jet
> database engine being incompatible with mobile devices. If this is the
> case I'd like to know because this project is currently dependent on VS
> being able to connect with our Access database. > > > >
>|||Thank you for the information. Unfortunately I have a follow up question. If I were to make an intermediate SQL database to function as the go between for a SQL Mobile database and a Microsoft access database, is such a solution even possible? Our organization recently purchased VS 2005 on the assumption that it would be easy to use the .NET framework to develop a mobile app that would easily transfer data to an access database. Now with this new information its quite possible the data will go SQL Mobile -> SQL Database -> MS Access database.
Footnote:
The SQL Database would just function as a temporary store to check data generated from receipts made in the mobile database. Once the checks are done the corrected data would be passed to the MS Access database. Then the SQL database would wipe itself clean.
|||
Yes, but it makes no sense. To accomplish that you would have to purchase SQL 2000/2005 and pay CALs per device (or per CPU license). If that’s acceptable, simply get rid of Access and use much more powerful SQL you've paid for.
Alternative solution is to use Web Service to talk to Access database. Create couple WEB methods to talk to access and call them from device. Something like this should do:
DataSet GetAccessData(String command, …)
{
// Connect to Access DB here, execute command and populate data set with returned data.
return dataSet;
}
Bool UpdateAccessData(DataSet dataSet, … )
{
// Connect to Access DB here and update DB with data from Data Set.
return success;
}
|||To: IlyaThe enterprise edition that we purchased of VBS 2005 came with SQL server 2005 and I just installed it upon learning that I can't communicate directly with the Access database. I'm not sure if we still have to pay the CALS per device though.
If I used these web service methods where would I write them? In the upload and download portion of the code for the mobile app?
|||
I believe it comes with 5 CALs (to be verified).
These WS methods would run on desktop via IIS which would host WS. To create them, please click ‘Create/Web Site’ and choose "ASP.Net Web Service". You'll get project skeleton, simply add code you need and run it. Decorate added methods with WebMethod attribute. This is desktop project, so you could use anything desktop has to offer.
Calling WS from device pretty much looks like a normal function call. All necessary code would be created for you automatically as soon as you add Web Reference to your project.
|||i want to connecting Access database on PDA ?i am setting Access database on PDA..
can i do ?
and how i doing?|||How would one connect to an access database inside the web method?
|||
while the .NET Compact Framework has no APIs to leverage Access databases, there is an open source set of APIs that support Access at www.opennetcf.org
there are also some commercial libraries available - use Google Advanced Groups search on microsoft.public.dotnet.framework.compactframework and look for "Access Database"
Darren
Access Database
When i set up an odbc connection to a access database on a network path. I
get an error saying the path is invalid. I am running SP2 . I can use the
same odbc datasource to connect using excel on the report server but not
using any reports.When you are running the report in production remember the path is relative
to the report server, not your workstation. perhaps that is the problem
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
news:8F43DC51-A2AF-46CE-962A-995CDD504BF8@.microsoft.com...
> When i set up an odbc connection to a access database on a network path. I
> get an error saying the path is invalid. I am running SP2 . I can use the
> same odbc datasource to connect using excel on the report server but not
> using any reports.|||i use the unc path to the shared folder on the network. It works fine from
the designer.
"Wayne Snyder" wrote:
> When you are running the report in production remember the path is relative
> to the report server, not your workstation. perhaps that is the problem
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
> news:8F43DC51-A2AF-46CE-962A-995CDD504BF8@.microsoft.com...
> > When i set up an odbc connection to a access database on a network path. I
> > get an error saying the path is invalid. I am running SP2 . I can use the
> > same odbc datasource to connect using excel on the report server but not
> > using any reports.
>
>|||Yes, but does the account the report is running under have rights to that
unc path?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
news:1FD8B93F-0179-4892-A787-11E3C904CB1A@.microsoft.com...
>i use the unc path to the shared folder on the network. It works fine from
> the designer.
> "Wayne Snyder" wrote:
>> When you are running the report in production remember the path is
>> relative
>> to the report server, not your workstation. perhaps that is the problem
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
>> news:8F43DC51-A2AF-46CE-962A-995CDD504BF8@.microsoft.com...
>> > When i set up an odbc connection to a access database on a network
>> > path. I
>> > get an error saying the path is invalid. I am running SP2 . I can use
>> > the
>> > same odbc datasource to connect using excel on the report server but
>> > not
>> > using any reports.
>>|||I have a similar problem, which is almost certainly a permissions problem in
the target shared directory. When you say "Account the report is running
under" are you talking about stored credentials in the RDS (which do not seem
to work) or a SYSTEM account that RS is using to try to access this shared
dir. I have had no luck whatsoever so any assistance tyou could render would
be most appreciated.
Thanks
Mark
"Bruce L-C [MVP]" wrote:
> Yes, but does the account the report is running under have rights to that
> unc path?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
> news:1FD8B93F-0179-4892-A787-11E3C904CB1A@.microsoft.com...
> >i use the unc path to the shared folder on the network. It works fine from
> > the designer.
> >
> > "Wayne Snyder" wrote:
> >
> >> When you are running the report in production remember the path is
> >> relative
> >> to the report server, not your workstation. perhaps that is the problem
> >>
> >> --
> >> Wayne Snyder, MCDBA, SQL Server MVP
> >> Mariner, Charlotte, NC
> >> www.mariner-usa.com
> >> (Please respond only to the newsgroups.)
> >>
> >> I support the Professional Association of SQL Server (PASS) and it's
> >> community of SQL Server professionals.
> >> www.sqlpass.org
> >>
> >> "Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
> >> news:8F43DC51-A2AF-46CE-962A-995CDD504BF8@.microsoft.com...
> >> > When i set up an odbc connection to a access database on a network
> >> > path. I
> >> > get an error saying the path is invalid. I am running SP2 . I can use
> >> > the
> >> > same odbc datasource to connect using excel on the report server but
> >> > not
> >> > using any reports.
> >>
> >>
> >>
>
>sql
get an error saying the path is invalid. I am running SP2 . I can use the
same odbc datasource to connect using excel on the report server but not
using any reports.When you are running the report in production remember the path is relative
to the report server, not your workstation. perhaps that is the problem
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
news:8F43DC51-A2AF-46CE-962A-995CDD504BF8@.microsoft.com...
> When i set up an odbc connection to a access database on a network path. I
> get an error saying the path is invalid. I am running SP2 . I can use the
> same odbc datasource to connect using excel on the report server but not
> using any reports.|||i use the unc path to the shared folder on the network. It works fine from
the designer.
"Wayne Snyder" wrote:
> When you are running the report in production remember the path is relative
> to the report server, not your workstation. perhaps that is the problem
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
> news:8F43DC51-A2AF-46CE-962A-995CDD504BF8@.microsoft.com...
> > When i set up an odbc connection to a access database on a network path. I
> > get an error saying the path is invalid. I am running SP2 . I can use the
> > same odbc datasource to connect using excel on the report server but not
> > using any reports.
>
>|||Yes, but does the account the report is running under have rights to that
unc path?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
news:1FD8B93F-0179-4892-A787-11E3C904CB1A@.microsoft.com...
>i use the unc path to the shared folder on the network. It works fine from
> the designer.
> "Wayne Snyder" wrote:
>> When you are running the report in production remember the path is
>> relative
>> to the report server, not your workstation. perhaps that is the problem
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
>> news:8F43DC51-A2AF-46CE-962A-995CDD504BF8@.microsoft.com...
>> > When i set up an odbc connection to a access database on a network
>> > path. I
>> > get an error saying the path is invalid. I am running SP2 . I can use
>> > the
>> > same odbc datasource to connect using excel on the report server but
>> > not
>> > using any reports.
>>|||I have a similar problem, which is almost certainly a permissions problem in
the target shared directory. When you say "Account the report is running
under" are you talking about stored credentials in the RDS (which do not seem
to work) or a SYSTEM account that RS is using to try to access this shared
dir. I have had no luck whatsoever so any assistance tyou could render would
be most appreciated.
Thanks
Mark
"Bruce L-C [MVP]" wrote:
> Yes, but does the account the report is running under have rights to that
> unc path?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
> news:1FD8B93F-0179-4892-A787-11E3C904CB1A@.microsoft.com...
> >i use the unc path to the shared folder on the network. It works fine from
> > the designer.
> >
> > "Wayne Snyder" wrote:
> >
> >> When you are running the report in production remember the path is
> >> relative
> >> to the report server, not your workstation. perhaps that is the problem
> >>
> >> --
> >> Wayne Snyder, MCDBA, SQL Server MVP
> >> Mariner, Charlotte, NC
> >> www.mariner-usa.com
> >> (Please respond only to the newsgroups.)
> >>
> >> I support the Professional Association of SQL Server (PASS) and it's
> >> community of SQL Server professionals.
> >> www.sqlpass.org
> >>
> >> "Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
> >> news:8F43DC51-A2AF-46CE-962A-995CDD504BF8@.microsoft.com...
> >> > When i set up an odbc connection to a access database on a network
> >> > path. I
> >> > get an error saying the path is invalid. I am running SP2 . I can use
> >> > the
> >> > same odbc datasource to connect using excel on the report server but
> >> > not
> >> > using any reports.
> >>
> >>
> >>
>
>sql
Access connectivity to SQL Tables (Urgent)
Please let me know whether Microsoft Access(2000), running on our office
computers, will be able to link (over the Internet) to the tables in our SQL
Server (2000) database on your server.If the clients running Access can access the SQL Server,
including the proper ports being open, authentication issues
resolved then yes you could link the tables. Whether you
would want to or not is a different story.
-Sue
On Fri, 7 Apr 2006 02:26:03 -0700, pcsmitpra
<pcsmitpra@.discussions.microsoft.com> wrote:
>Please let me know whether Microsoft Access(2000), running on our office
>computers, will be able to link (over the Internet) to the tables in our SQ
L
>Server (2000) database on your server.
computers, will be able to link (over the Internet) to the tables in our SQL
Server (2000) database on your server.If the clients running Access can access the SQL Server,
including the proper ports being open, authentication issues
resolved then yes you could link the tables. Whether you
would want to or not is a different story.
-Sue
On Fri, 7 Apr 2006 02:26:03 -0700, pcsmitpra
<pcsmitpra@.discussions.microsoft.com> wrote:
>Please let me know whether Microsoft Access(2000), running on our office
>computers, will be able to link (over the Internet) to the tables in our SQ
L
>Server (2000) database on your server.
Monday, March 19, 2012
Access + ODBC + MS SQL on WAN
Hi, there !
Can anybody tell me why the application MS ACCESS 2002
front-end + MS SQL Server 2000 back-end with the tables
linked via ODBC is running perfectly well on LAN, and does
not run on WAN ?
The firewall and security issues excluded.
Many thanks,
Dmitri KalininWhat do you mean by 'Does not run'
Is it slow? Crashes?
"Dmitri" <kalinin@.dblink.co.nz> wrote in message
news:081301c33f95$46100d80$a101280a@.phx.gbl...
> Hi, there !
> Can anybody tell me why the application MS ACCESS 2002
> front-end + MS SQL Server 2000 back-end with the tables
> linked via ODBC is running perfectly well on LAN, and does
> not run on WAN ?
> The firewall and security issues excluded.
> Many thanks,
> Dmitri Kalinin|||"Dmitri" <kalinin@.dblink.co.nz> wrote in message news:<081301c33f95$46100d80$a101280a@.phx.gbl>...
> Hi, there !
> Can anybody tell me why the application MS ACCESS 2002
> front-end + MS SQL Server 2000 back-end with the tables
> linked via ODBC is running perfectly well on LAN, and does
> not run on WAN ?
> The firewall and security issues excluded.
> Many thanks,
> Dmitri Kalinin
I'd guess there are different network protocols involved somehow.
Or maybe DNS.
Perhaps you need a LMhosts file (update)?
Can anybody tell me why the application MS ACCESS 2002
front-end + MS SQL Server 2000 back-end with the tables
linked via ODBC is running perfectly well on LAN, and does
not run on WAN ?
The firewall and security issues excluded.
Many thanks,
Dmitri KalininWhat do you mean by 'Does not run'
Is it slow? Crashes?
"Dmitri" <kalinin@.dblink.co.nz> wrote in message
news:081301c33f95$46100d80$a101280a@.phx.gbl...
> Hi, there !
> Can anybody tell me why the application MS ACCESS 2002
> front-end + MS SQL Server 2000 back-end with the tables
> linked via ODBC is running perfectly well on LAN, and does
> not run on WAN ?
> The firewall and security issues excluded.
> Many thanks,
> Dmitri Kalinin|||"Dmitri" <kalinin@.dblink.co.nz> wrote in message news:<081301c33f95$46100d80$a101280a@.phx.gbl>...
> Hi, there !
> Can anybody tell me why the application MS ACCESS 2002
> front-end + MS SQL Server 2000 back-end with the tables
> linked via ODBC is running perfectly well on LAN, and does
> not run on WAN ?
> The firewall and security issues excluded.
> Many thanks,
> Dmitri Kalinin
I'd guess there are different network protocols involved somehow.
Or maybe DNS.
Perhaps you need a LMhosts file (update)?
Access .adp :How to INSERT all but KEY violations
I am trying to append records from one table to another in a db running on
MSDE, knowing fullwell that some of the data in the source will be
duplicates of that in the destination table's pk.
What I would like to happen is to have the stored procedure plunk in all
records that don't violate the constraint
and silently let the duplicate info fall by the wayside. The trouble is SQL
server seems to abort the whole procedure if
even a single record violates the constraint.
MSDE, knowing fullwell that some of the data in the source will be
duplicates of that in the destination table's pk.
What I would like to happen is to have the stored procedure plunk in all
records that don't violate the constraint
and silently let the duplicate info fall by the wayside. The trouble is SQL
server seems to abort the whole procedure if
even a single record violates the constraint.
In a regular Access mdb, an INSERT statement (append query) would do just
that. Of course it warns you of the violation but a DoCmd.SetWarnings FALSE
takes care of that.
Any ideas as to what I need to do to achieve that same thing?For example:
INSERT INTO TargetTable (key_col, col1, col2, ...)
SELECT S.key_col, S.col1, S.col2, ...
FROM SourceTable AS S
LEFT JOIN TargetTable AS T
ON S.key_col = T.key_col
WHERE T.key_col IS NULL
(where key_col is the primary key).
--
David Portas
SQL Server MVP
--
Sunday, March 11, 2012
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
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
accent insensitive full text search
I am running SQL 2000 SP3a on Windows 2000.
Is it possible to obtain accent insensitive results from full-text searches
so that a search for caf will return all records for caf and cafe as if
these were the same?
In the four queries below queries 2a and 2b return the same results as each
other whereas queries 1a and 1b return different results from each other.
1a. select * from MyTable where CONTAINS(*,'cafe')
1b. select * from MyTable where CONTAINS(*,'caf')
2a. select * from MyTable where searchtext like '%cafe%'
2b. select * from Mytable where searchtext like '%caf%'
Thanks
Joseph
No,it is not possible.
This issue has already been discussed in detail several times, do a search
in this newsgroup.
Matthias
"Joseph Tervit" <joseph@.company-net.com> wrote in message
news:eaRfNYeMEHA.1340@.TK2MSFTNGP12.phx.gbl...
> I am running SQL 2000 SP3a on Windows 2000.
> Is it possible to obtain accent insensitive results from full-text
searches
> so that a search for caf will return all records for caf and cafe as if
> these were the same?
> In the four queries below queries 2a and 2b return the same results as
each
> other whereas queries 1a and 1b return different results from each other.
> 1a. select * from MyTable where CONTAINS(*,'cafe')
> 1b. select * from MyTable where CONTAINS(*,'caf')
> 2a. select * from MyTable where searchtext like '%cafe%'
> 2b. select * from Mytable where searchtext like '%caf%'
> Thanks
> Joseph
>
|||Joseph,
Matthias, yes this has been discussed many, many (too many) times in this
newsgroup, IMHO. However, with the new Microsoft newsgroup msg roll-off
policy, the past replies might not be searchable (except via Google groups,
of course! ;-). One easy solution is to simply install SharePoint Server
2001 or 2003 as either will upgrade the MSSearch service to the SPPS version
that does support accent insensitive search.
Regards,
John
"Matthias HALDIMANN" <matthias.haldimann@.epfl.ch> wrote in message
news:409883a9$1@.epflnews.epfl.ch...[vbcol=seagreen]
> No,it is not possible.
> This issue has already been discussed in detail several times, do a search
> in this newsgroup.
> Matthias
>
> "Joseph Tervit" <joseph@.company-net.com> wrote in message
> news:eaRfNYeMEHA.1340@.TK2MSFTNGP12.phx.gbl...
> searches
if[vbcol=seagreen]
> each
other.
>
|||Hi,
I have two questions relating to your suggestion of installing sharepoint.
1. will this work for both sql fts and indexing server fts
(I currently have a file system catolog that I have linked into slq server, I do joined queries that search that catalog and and sql catalog for the same phrases)
2. if so, hwo do I rebuild the catalogs to be case insensitive?
thanks in advance
randyr
"John Kane" wrote:
> Joseph,
> Matthias, yes this has been discussed many, many (too many) times in this
> newsgroup, IMHO. However, with the new Microsoft newsgroup msg roll-off
> policy, the past replies might not be searchable (except via Google groups,
> of course! ;-). One easy solution is to simply install SharePoint Server
> 2001 or 2003 as either will upgrade the MSSearch service to the SPPS version
> that does support accent insensitive search.
> Regards,
> John
>
>
> "Matthias HALDIMANN" <matthias.haldimann@.epfl.ch> wrote in message
> news:409883a9$1@.epflnews.epfl.ch...
> if
> other.
>
>
|||Randyr,
Hmm... a blast from the past... a past posting of mine...
1. will this work for both sql fts and indexing server fts
A. No. While the SQL FTS and Indexing Services use the same basic underlying
technology, they are implemented differently and SharePoint was developed
after both of these products were shipped.
2. if so, how do I rebuild the catalogs to be case insensitive?
A. You cannot rebuild a SQL Server 2000 FT Catalog to be "case insensitive"
or "accent insensitive" as I'm assuming that's what you meant to say...
.. However, this will be possible with SQL Server 2005 (Yukon) this will be
supported via T-SQL: "CREATE FULLTEXT CATALOG FT_Catalog_Name WITH
ACCENT_SENSITIVITY = OFF AS DEFAULT"
Either way, I'd highly recommend that you test this functionality this in a
test environment FIRST before implementing it on your production servers.
Regards,
John
"randyr" <randyr@.online.nospam> wrote in message
news:70BBE3FA-EA50-4D76-9E45-F52B3E5812CF@.microsoft.com...
> Hi,
> I have two questions relating to your suggestion of installing sharepoint.
> 1. will this work for both sql fts and indexing server fts
> (I currently have a file system catolog that I have linked into slq
server, I do joined queries that search that catalog and and sql catalog for
the same phrases)[vbcol=seagreen]
> 2. if so, hwo do I rebuild the catalogs to be case insensitive?
> thanks in advance
> --
> randyr
>
> "John Kane" wrote:
this[vbcol=seagreen]
groups,[vbcol=seagreen]
version[vbcol=seagreen]
search[vbcol=seagreen]
as[vbcol=seagreen]
as[vbcol=seagreen]
Is it possible to obtain accent insensitive results from full-text searches
so that a search for caf will return all records for caf and cafe as if
these were the same?
In the four queries below queries 2a and 2b return the same results as each
other whereas queries 1a and 1b return different results from each other.
1a. select * from MyTable where CONTAINS(*,'cafe')
1b. select * from MyTable where CONTAINS(*,'caf')
2a. select * from MyTable where searchtext like '%cafe%'
2b. select * from Mytable where searchtext like '%caf%'
Thanks
Joseph
No,it is not possible.
This issue has already been discussed in detail several times, do a search
in this newsgroup.
Matthias
"Joseph Tervit" <joseph@.company-net.com> wrote in message
news:eaRfNYeMEHA.1340@.TK2MSFTNGP12.phx.gbl...
> I am running SQL 2000 SP3a on Windows 2000.
> Is it possible to obtain accent insensitive results from full-text
searches
> so that a search for caf will return all records for caf and cafe as if
> these were the same?
> In the four queries below queries 2a and 2b return the same results as
each
> other whereas queries 1a and 1b return different results from each other.
> 1a. select * from MyTable where CONTAINS(*,'cafe')
> 1b. select * from MyTable where CONTAINS(*,'caf')
> 2a. select * from MyTable where searchtext like '%cafe%'
> 2b. select * from Mytable where searchtext like '%caf%'
> Thanks
> Joseph
>
|||Joseph,
Matthias, yes this has been discussed many, many (too many) times in this
newsgroup, IMHO. However, with the new Microsoft newsgroup msg roll-off
policy, the past replies might not be searchable (except via Google groups,
of course! ;-). One easy solution is to simply install SharePoint Server
2001 or 2003 as either will upgrade the MSSearch service to the SPPS version
that does support accent insensitive search.
Regards,
John
"Matthias HALDIMANN" <matthias.haldimann@.epfl.ch> wrote in message
news:409883a9$1@.epflnews.epfl.ch...[vbcol=seagreen]
> No,it is not possible.
> This issue has already been discussed in detail several times, do a search
> in this newsgroup.
> Matthias
>
> "Joseph Tervit" <joseph@.company-net.com> wrote in message
> news:eaRfNYeMEHA.1340@.TK2MSFTNGP12.phx.gbl...
> searches
if[vbcol=seagreen]
> each
other.
>
|||Hi,
I have two questions relating to your suggestion of installing sharepoint.
1. will this work for both sql fts and indexing server fts
(I currently have a file system catolog that I have linked into slq server, I do joined queries that search that catalog and and sql catalog for the same phrases)
2. if so, hwo do I rebuild the catalogs to be case insensitive?
thanks in advance
randyr
"John Kane" wrote:
> Joseph,
> Matthias, yes this has been discussed many, many (too many) times in this
> newsgroup, IMHO. However, with the new Microsoft newsgroup msg roll-off
> policy, the past replies might not be searchable (except via Google groups,
> of course! ;-). One easy solution is to simply install SharePoint Server
> 2001 or 2003 as either will upgrade the MSSearch service to the SPPS version
> that does support accent insensitive search.
> Regards,
> John
>
>
> "Matthias HALDIMANN" <matthias.haldimann@.epfl.ch> wrote in message
> news:409883a9$1@.epflnews.epfl.ch...
> if
> other.
>
>
|||Randyr,
Hmm... a blast from the past... a past posting of mine...
1. will this work for both sql fts and indexing server fts
A. No. While the SQL FTS and Indexing Services use the same basic underlying
technology, they are implemented differently and SharePoint was developed
after both of these products were shipped.
2. if so, how do I rebuild the catalogs to be case insensitive?
A. You cannot rebuild a SQL Server 2000 FT Catalog to be "case insensitive"
or "accent insensitive" as I'm assuming that's what you meant to say...
.. However, this will be possible with SQL Server 2005 (Yukon) this will be
supported via T-SQL: "CREATE FULLTEXT CATALOG FT_Catalog_Name WITH
ACCENT_SENSITIVITY = OFF AS DEFAULT"
Either way, I'd highly recommend that you test this functionality this in a
test environment FIRST before implementing it on your production servers.
Regards,
John
"randyr" <randyr@.online.nospam> wrote in message
news:70BBE3FA-EA50-4D76-9E45-F52B3E5812CF@.microsoft.com...
> Hi,
> I have two questions relating to your suggestion of installing sharepoint.
> 1. will this work for both sql fts and indexing server fts
> (I currently have a file system catolog that I have linked into slq
server, I do joined queries that search that catalog and and sql catalog for
the same phrases)[vbcol=seagreen]
> 2. if so, hwo do I rebuild the catalogs to be case insensitive?
> thanks in advance
> --
> randyr
>
> "John Kane" wrote:
this[vbcol=seagreen]
groups,[vbcol=seagreen]
version[vbcol=seagreen]
search[vbcol=seagreen]
as[vbcol=seagreen]
as[vbcol=seagreen]
Thursday, March 8, 2012
Absent SQL Counters under X64 environment
all,
Am missing SQL counters when I do perfmon from my desktop to SQL 2000 (x64)
database server. Is this due to an incorrect executable running from the
desktop?
thanks.
jl
There is a post on this problem at:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12363
Hope it helps.
"John L" wrote:
> all,
> Am missing SQL counters when I do perfmon from my desktop to SQL 2000 (x64)
> database server. Is this due to an incorrect executable running from the
> desktop?
> thanks.
> --
> jl
Am missing SQL counters when I do perfmon from my desktop to SQL 2000 (x64)
database server. Is this due to an incorrect executable running from the
desktop?
thanks.
jl
There is a post on this problem at:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12363
Hope it helps.
"John L" wrote:
> all,
> Am missing SQL counters when I do perfmon from my desktop to SQL 2000 (x64)
> database server. Is this due to an incorrect executable running from the
> desktop?
> thanks.
> --
> jl
Absent SQL Counters under X64 environment
all,
Am missing SQL counters when I do perfmon from my desktop to SQL 2000 (x64)
database server. Is this due to an incorrect executable running from the
desktop?
thanks.
--
jlThere is a post on this problem at:
http://www.sql-server-performance.c...?TOPIC_ID=12363
Hope it helps.
"John L" wrote:
> all,
> Am missing SQL counters when I do perfmon from my desktop to SQL 2000 (x64
)
> database server. Is this due to an incorrect executable running from the
> desktop?
> thanks.
> --
> jl
Am missing SQL counters when I do perfmon from my desktop to SQL 2000 (x64)
database server. Is this due to an incorrect executable running from the
desktop?
thanks.
--
jlThere is a post on this problem at:
http://www.sql-server-performance.c...?TOPIC_ID=12363
Hope it helps.
"John L" wrote:
> all,
> Am missing SQL counters when I do perfmon from my desktop to SQL 2000 (x64
)
> database server. Is this due to an incorrect executable running from the
> desktop?
> thanks.
> --
> jl
Absent SQL Counters under X64 environment
all,
Am missing SQL counters when I do perfmon from my desktop to SQL 2000 (x64)
database server. Is this due to an incorrect executable running from the
desktop?
thanks.
--
jlThere is a post on this problem at:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12363
Hope it helps.
"John L" wrote:
> all,
> Am missing SQL counters when I do perfmon from my desktop to SQL 2000 (x64)
> database server. Is this due to an incorrect executable running from the
> desktop?
> thanks.
> --
> jl
Am missing SQL counters when I do perfmon from my desktop to SQL 2000 (x64)
database server. Is this due to an incorrect executable running from the
desktop?
thanks.
--
jlThere is a post on this problem at:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12363
Hope it helps.
"John L" wrote:
> all,
> Am missing SQL counters when I do perfmon from my desktop to SQL 2000 (x64)
> database server. Is this due to an incorrect executable running from the
> desktop?
> thanks.
> --
> jl
Tuesday, March 6, 2012
About the sql 2005 client software
Hello,
Assume that I have a ms sql 2005 server running,
and the clients wants to connect to the database through their Windows XP desktops,
is there any sql 2005 client software that can install on the clients desktop for them to use? (e.g. like MySQLFront or PLSQL)
so that they can input sql statement or store procedure on the client side to make query to the database.
Does any things should notice during installation?
Thanks for helping!!
Ah, if you write a program (or use a program) that can create an OLE DB or .NET SqlClient connection, you can connect to SQL Server 2005--assuming that it's enabled to see the LAN (it's disabled by default). Even VB6 or older programs can use the SQL Native Client with an OLE DB-enabled application to connect to SQL Server 2005.
hth
|||Hi William,
Do you know is this possible for using a free Microsoft SQL Server 2005 Express Edition as a client side interface, intall it on Windows XP. Use it to connect to a sql 2005 standard server to make query and update information on table or views, by using the user's sql login name & password of the sql 2005 standard server to authenticate.
Or I must use win2003 remote desktop with terminal server client license to let the users to remote to the sql 2005 standard server directly & logon locally to achieve this goal.
Thanks!
About the security
Dear all,
How to indetify if there is a trace running against a sql server or not? I
will try to explain better. I've got lots of sql2k remote servers added into
my EMC client. Up to here that's fine. But due to a big complexity of our
organization sometimes oneself have serious doubts about of the "do and
donts" (you could think that's ridiculous or even amazing but that's the
truth)
If I perfectly know that auditing process are running then I don't want do
any SELECT, obviously.
"exec sp_trace_getdata 2, 0" in current activity and along with 'Sql
Profiler' obvioulsy say us that Sql tool for that is running. But the
question is, if were another tool disguissed doing that?
You never will have the total security of that anybody is spying one server.
thanks in advance,
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)It may be going off at a bit of a tangent, but if you need to be that
sure that no-ones going to capture your SQL statements, have you
considered encrypting the statement, sending that to an SP that will
decrypt it and then run it using sp_executesql. This way the actual
statement should never be seen as it is executed as part of the outer
stored procedure. Only I can't see why if someone really wanted to get
at your statements they couldn't just read the packets going through,
and never actually interface with the SQL server (though I admit I do
not know how SQL server actually sends its instructions - perhaps they
are already encrypted while being sent).|||fn_trace_getinfo
"Enric" wrote:
> Dear all,
> How to indetify if there is a trace running against a sql server or not? I
> will try to explain better. I've got lots of sql2k remote servers added in
to
> my EMC client. Up to here that's fine. But due to a big complexity of our
> organization sometimes oneself have serious doubts about of the "do and
> donts" (you could think that's ridiculous or even amazing but that's the
> truth)
> If I perfectly know that auditing process are running then I don't want do
> any SELECT, obviously.
> "exec sp_trace_getdata 2, 0" in current activity and along with 'Sql
> Profiler' obvioulsy say us that Sql tool for that is running. But the
> question is, if were another tool disguissed doing that?
> You never will have the total security of that anybody is spying one serve
r.
> thanks in advance,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)
How to indetify if there is a trace running against a sql server or not? I
will try to explain better. I've got lots of sql2k remote servers added into
my EMC client. Up to here that's fine. But due to a big complexity of our
organization sometimes oneself have serious doubts about of the "do and
donts" (you could think that's ridiculous or even amazing but that's the
truth)
If I perfectly know that auditing process are running then I don't want do
any SELECT, obviously.
"exec sp_trace_getdata 2, 0" in current activity and along with 'Sql
Profiler' obvioulsy say us that Sql tool for that is running. But the
question is, if were another tool disguissed doing that?
You never will have the total security of that anybody is spying one server.
thanks in advance,
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)It may be going off at a bit of a tangent, but if you need to be that
sure that no-ones going to capture your SQL statements, have you
considered encrypting the statement, sending that to an SP that will
decrypt it and then run it using sp_executesql. This way the actual
statement should never be seen as it is executed as part of the outer
stored procedure. Only I can't see why if someone really wanted to get
at your statements they couldn't just read the packets going through,
and never actually interface with the SQL server (though I admit I do
not know how SQL server actually sends its instructions - perhaps they
are already encrypted while being sent).|||fn_trace_getinfo
"Enric" wrote:
> Dear all,
> How to indetify if there is a trace running against a sql server or not? I
> will try to explain better. I've got lots of sql2k remote servers added in
to
> my EMC client. Up to here that's fine. But due to a big complexity of our
> organization sometimes oneself have serious doubts about of the "do and
> donts" (you could think that's ridiculous or even amazing but that's the
> truth)
> If I perfectly know that auditing process are running then I don't want do
> any SELECT, obviously.
> "exec sp_trace_getdata 2, 0" in current activity and along with 'Sql
> Profiler' obvioulsy say us that Sql tool for that is running. But the
> question is, if were another tool disguissed doing that?
> You never will have the total security of that anybody is spying one serve
r.
> thanks in advance,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)
Friday, February 24, 2012
About SQL Server 7.0 upgrade to 2005
There is a system running in win NT with DB SQL server 7.0, and now want to upgrade DB SQL server 7.0 to 2005 and run at window server 2003.
I want to konw that is there any impact to the system at DB part ? all the original script can be run normal when DB upgraded to 2005?
Moved to more appropriate forum.|||
Leopardxp wrote:
There is a system running in win NT with DB SQL server 7.0, and now want to upgrade DB SQL server 7.0 to 2005 and run at window server 2003.
I want to konw that is there any impact to the system at DB part ? all the original script can be run normal when DB upgraded to 2005?
No they will not run, there is so much difference between the two, I think you need to create an integration services package to move the database so you can deal with issues before moving to 2005. Another option is to restore a backup with 80 compatibility but that leaves the database in a state that SQL Server 2005 will complain about certain operations not suported because your database is in 2000 compatibility level. It is not complicated just more work. Hope this helps.
|||Before upgrading your production system from SQL 7 to SQL 2005 (or from SQL 2000 to SQL 2005) you will want to do a massive amount of testing to ensure that everything that you are doing is still supported in SQL 2005, and that everything that you expect to exist still exists. Some system objects (such as sysobjects) have been changed or removed.
Also with the upgrade to SQL 2005 there may be many places where your T/SQL code can be optomized for SQL 2005 using new commands, or functions.
About SQL Server 2005 Express
I developed a small application to manage inventory, accounting, payroll and so, for up to 5 users, running on SQL DB. Now I have some people interested for the software, they want to use it in their small companies. If I sell them the solution, do they have to license SQL DB ? or can they use express edition for free ?
You can distribute (or they can download and install) SQL 2005 Express. It is FREE.
You may wish to check out this resource:
SQL Server 2005 Express Redistribution
http://www.microsoft.com/sql/editions/express/redistregister.mspx
Subscribe to:
Posts (Atom)