I have a web application in one server and reporting service in another
server. This web application is to list out report items. When I call web
application in any client machine, error is always shown - 'The request
failed with HTTP status 401: Access Denied'. But if I install the reporting
service and web application in same server, there is no problem. Can anyone
help me ?Open the RSWebApplication.config file with a text editor. The first few
lines of the file will be similar to the following:
<Configuration>
<UI>
<ReportServerUrl>http://servername/ReportServer</ReportServerUrl>
</UI>
Replace the text between <ReportServerUrl> and </ReportServerUrl> with the
complete address that should be used to access the Reports website like,
<Configuration>
<UI>
<ReportServerUrl>http://www.xyzname.com/ReportServer</ReportServerUrl>
</UI>
or with IP address
<Configuration>
<UI>
<ReportServerUrl>http://192.162.x.y/ReportServer</ReportServerUrl>
</UI>
Hope this helps,
Barbaros Saglamtimur
MCDBA, MCAD
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:CF1F5CDB-596B-4015-8FE4-8D9B7D85D803@.microsoft.com...
>I have a web application in one server and reporting service in another
> server. This web application is to list out report items. When I call
> web
> application in any client machine, error is always shown - 'The request
> failed with HTTP status 401: Access Denied'. But if I install the
> reporting
> service and web application in same server, there is no problem. Can
> anyone
> help me ?
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Tuesday, March 27, 2012
Access DB vs. SQL server
Hi, I'm developing a web application that could have up to about 700 people connected to it at the same time. I love MS Access so I would love to be able to use an access database here, but I'm concerned that it won't be able to handle all the users. Should I just create an SQL server database for this application or am I worrying for nothing here? I'll be using Visual studio .NET 2003 to create this app.
ThanksPersonally, unless the application makes very light use of the database, Access will get crushed by 700 simultaneous users, even Web users.|||Thanks. Given that I'm only comfortable with Access right now, do you think SQL server is the way to go for me?|||I personally like SQL Server. The terminology and tools are different, even though they are both from Microsoft, but SQL Server is about the easiest to use full server based database system I have used. You can attach to the SQL Server database to edit/query data if you are more comfortable using Access, though for database schema changes, etc, you will need to use Enterprise manager or similar tools.
ThanksPersonally, unless the application makes very light use of the database, Access will get crushed by 700 simultaneous users, even Web users.|||Thanks. Given that I'm only comfortable with Access right now, do you think SQL server is the way to go for me?|||I personally like SQL Server. The terminology and tools are different, even though they are both from Microsoft, but SQL Server is about the easiest to use full server based database system I have used. You can attach to the SQL Server database to edit/query data if you are more comfortable using Access, though for database schema changes, etc, you will need to use Enterprise manager or similar tools.
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 Crashes Filtering a Linked SQL Table on a date field
I have an MS Access 2002 application that is distributed to a number of PCs around our office. The data for this application is stored on a central SQL Server that is linked in through ODBC.
This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.
Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.
I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.
Has anyone run across this before?
Thanks!
-BenWhat is the operating system on the new PC?
Is is connecting directly to the network?
What is the SQL Server ODBC driver version #?
The other thing I would do is find the developer that designed the application in Access and smack him in the back of the head for trying to develop a multi-user application in Access.
There are many many other alternatives that would work better and be much much faster.
This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.
Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.
I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.
Has anyone run across this before?
Thanks!
-BenWhat is the operating system on the new PC?
Is is connecting directly to the network?
What is the SQL Server ODBC driver version #?
The other thing I would do is find the developer that designed the application in Access and smack him in the back of the head for trying to develop a multi-user application in Access.
There are many many other alternatives that would work better and be much much faster.
Access C# code from Integration services
I have an application in C# in which i have few events like download,Import and I have written all the code for that and its working fine but I have to schedule these processes so that I need not to run it manually daily. I am trying to write an Integration services package through which I could handle the required event, without rewriting the whole code.
Please Reply.
Thanks,
Pooja
bajaj.puja@.gmail.com
Hi Pooja B,You can access a process (an exe file) with Execute Process Task - is this what you mean? If you just want to schedule the process maybe you could use "Scheduled Tasks" in windows.
Another aprouch is to ajust the code to fit into a Custom Task - how to do this is a little bit of work...
Regards
Simon
|||You can also use a script task to reference a .net assembly. Not sure exactly what you are trying to accomplish though, so I'm not sure if this is the right approach.|||
Pooja B wrote:
I have an application in C# in which i have few events like download,Import and I have written all the code for that and its working fine but I have to schedule these processes so that I need not to run it manually daily. I am trying to write an Integration services package through which I could handle the required event, without rewriting the whole code.
Please Reply.
Thanks,
Pooja
bajaj.puja@.gmail.com
Pooja,
See my reply to your previous post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1423350&SiteID=1
-Jamie
Thursday, March 22, 2012
Access as front end, SQL Server as backend
What are the pro's and con's of an application that uses
Access as the user interface and SQL Server as the backend
database, compared with if VB is used to develop the front
end instead?DBA,
VB is more flexible but may require more expensive resources. Using VB
is more likely, but not necessarily, to produce a more stable, scalable
application. I would recommend using a .NET language such as VB.NET
It also depends on the skilled resources you have available to draw on.
If you only have Access developers, then you are limited to those,
unless you want to pay for a VB.NET developer.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DBA wrote:
> What are the pro's and con's of an application that uses
> Access as the user interface and SQL Server as the backend
> database, compared with if VB is used to develop the front
> end instead?|||In addition to what Mark said, you have to consider what your app is
being used for, how many concurrent users will be updating data, what
is the deployment model (intranet, internet, local), is it an OLAP or
OLTP app or a combo, do you need reports, how much budget, existing
expertise, etc. The suitability of any particular client application
is highly dependent on the answers to these questions. I'd recommend a
needs analysis where you formally go through the process of asking and
researching the answers to these questions.
--Mary
On Thu, 22 Jul 2004 21:17:13 -0700, "DBA"
<anonymous@.discussions.microsoft.com> wrote:
>What are the pro's and con's of an application that uses
>Access as the user interface and SQL Server as the backend
>database, compared with if VB is used to develop the front
>end instead?
Access as the user interface and SQL Server as the backend
database, compared with if VB is used to develop the front
end instead?DBA,
VB is more flexible but may require more expensive resources. Using VB
is more likely, but not necessarily, to produce a more stable, scalable
application. I would recommend using a .NET language such as VB.NET
It also depends on the skilled resources you have available to draw on.
If you only have Access developers, then you are limited to those,
unless you want to pay for a VB.NET developer.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DBA wrote:
> What are the pro's and con's of an application that uses
> Access as the user interface and SQL Server as the backend
> database, compared with if VB is used to develop the front
> end instead?|||In addition to what Mark said, you have to consider what your app is
being used for, how many concurrent users will be updating data, what
is the deployment model (intranet, internet, local), is it an OLAP or
OLTP app or a combo, do you need reports, how much budget, existing
expertise, etc. The suitability of any particular client application
is highly dependent on the answers to these questions. I'd recommend a
needs analysis where you formally go through the process of asking and
researching the answers to these questions.
--Mary
On Thu, 22 Jul 2004 21:17:13 -0700, "DBA"
<anonymous@.discussions.microsoft.com> wrote:
>What are the pro's and con's of an application that uses
>Access as the user interface and SQL Server as the backend
>database, compared with if VB is used to develop the front
>end instead?
Access as front end, SQL Server as backend
What are the pro's and con's of an application that uses
Access as the user interface and SQL Server as the backend
database, compared with if VB is used to develop the front
end instead?DBA,
VB is more flexible but may require more expensive resources. Using VB
is more likely, but not necessarily, to produce a more stable, scalable
application. I would recommend using a .NET language such as VB.NET
It also depends on the skilled resources you have available to draw on.
If you only have Access developers, then you are limited to those,
unless you want to pay for a VB.NET developer.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DBA wrote:
> What are the pro's and con's of an application that uses
> Access as the user interface and SQL Server as the backend
> database, compared with if VB is used to develop the front
> end instead?|||In addition to what Mark said, you have to consider what your app is
being used for, how many concurrent users will be updating data, what
is the deployment model (intranet, internet, local), is it an OLAP or
OLTP app or a combo, do you need reports, how much budget, existing
expertise, etc. The suitability of any particular client application
is highly dependent on the answers to these questions. I'd recommend a
needs analysis where you formally go through the process of asking and
researching the answers to these questions.
--Mary
On Thu, 22 Jul 2004 21:17:13 -0700, "DBA"
<anonymous@.discussions.microsoft.com> wrote:
>What are the pro's and con's of an application that uses
>Access as the user interface and SQL Server as the backend
>database, compared with if VB is used to develop the front
>end instead?
Access as the user interface and SQL Server as the backend
database, compared with if VB is used to develop the front
end instead?DBA,
VB is more flexible but may require more expensive resources. Using VB
is more likely, but not necessarily, to produce a more stable, scalable
application. I would recommend using a .NET language such as VB.NET
It also depends on the skilled resources you have available to draw on.
If you only have Access developers, then you are limited to those,
unless you want to pay for a VB.NET developer.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DBA wrote:
> What are the pro's and con's of an application that uses
> Access as the user interface and SQL Server as the backend
> database, compared with if VB is used to develop the front
> end instead?|||In addition to what Mark said, you have to consider what your app is
being used for, how many concurrent users will be updating data, what
is the deployment model (intranet, internet, local), is it an OLAP or
OLTP app or a combo, do you need reports, how much budget, existing
expertise, etc. The suitability of any particular client application
is highly dependent on the answers to these questions. I'd recommend a
needs analysis where you formally go through the process of asking and
researching the answers to these questions.
--Mary
On Thu, 22 Jul 2004 21:17:13 -0700, "DBA"
<anonymous@.discussions.microsoft.com> wrote:
>What are the pro's and con's of an application that uses
>Access as the user interface and SQL Server as the backend
>database, compared with if VB is used to develop the front
>end instead?
Access as front end, SQL Server as backend
What are the pro's and con's of an application that uses
Access as the user interface and SQL Server as the backend
database, compared with if VB is used to develop the front
end instead?
DBA,
VB is more flexible but may require more expensive resources. Using VB
is more likely, but not necessarily, to produce a more stable, scalable
application. I would recommend using a .NET language such as VB.NET
It also depends on the skilled resources you have available to draw on.
If you only have Access developers, then you are limited to those,
unless you want to pay for a VB.NET developer.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DBA wrote:
> What are the pro's and con's of an application that uses
> Access as the user interface and SQL Server as the backend
> database, compared with if VB is used to develop the front
> end instead?
|||In addition to what Mark said, you have to consider what your app is
being used for, how many concurrent users will be updating data, what
is the deployment model (intranet, internet, local), is it an OLAP or
OLTP app or a combo, do you need reports, how much budget, existing
expertise, etc. The suitability of any particular client application
is highly dependent on the answers to these questions. I'd recommend a
needs analysis where you formally go through the process of asking and
researching the answers to these questions.
--Mary
On Thu, 22 Jul 2004 21:17:13 -0700, "DBA"
<anonymous@.discussions.microsoft.com> wrote:
>What are the pro's and con's of an application that uses
>Access as the user interface and SQL Server as the backend
>database, compared with if VB is used to develop the front
>end instead?
Access as the user interface and SQL Server as the backend
database, compared with if VB is used to develop the front
end instead?
DBA,
VB is more flexible but may require more expensive resources. Using VB
is more likely, but not necessarily, to produce a more stable, scalable
application. I would recommend using a .NET language such as VB.NET
It also depends on the skilled resources you have available to draw on.
If you only have Access developers, then you are limited to those,
unless you want to pay for a VB.NET developer.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DBA wrote:
> What are the pro's and con's of an application that uses
> Access as the user interface and SQL Server as the backend
> database, compared with if VB is used to develop the front
> end instead?
|||In addition to what Mark said, you have to consider what your app is
being used for, how many concurrent users will be updating data, what
is the deployment model (intranet, internet, local), is it an OLAP or
OLTP app or a combo, do you need reports, how much budget, existing
expertise, etc. The suitability of any particular client application
is highly dependent on the answers to these questions. I'd recommend a
needs analysis where you formally go through the process of asking and
researching the answers to these questions.
--Mary
On Thu, 22 Jul 2004 21:17:13 -0700, "DBA"
<anonymous@.discussions.microsoft.com> wrote:
>What are the pro's and con's of an application that uses
>Access as the user interface and SQL Server as the backend
>database, compared with if VB is used to develop the front
>end instead?
ACCESS ADP TransferSpreadsheet
Ok...here's a good one.
I did try including dbo.TableName. That also doesn't work. Onething
that I've been talking over with coworkers is Office Versions. The
Test User account that I used, I also used on an Office 2k3 machine.
Everyone else is either using XP or 2K. I know that that shouldn't be
an issue, but right now that's the only thing that's different.
qualified table name correctly.
i have not found a fix except to run a stored proc that changes the
object owner on the database.
quick, so we just used SQL Reporting Services to push the export.
I wrote an ADP application in ACCESS XP with a SQL 2000 back end.
Works Great! Recently we did a server migration and transfered the
database to another server. Setup all the groups and security without
a hitch. My users change the Connection to point to the new server and
all of a sudden the TransferSpreadsheet function that I'm using in two
seperate procedures starts to error saying that it can't see the table.
So I look...and there it is. The function doesn't see it though.
I try it on my machine and it works fine, but I have Local Admin rights
on the server though. So we start looking around. What we found is
that only those with Local Admin Rights and Server Administration
rights seem to be able to execute the procedure so that the
TransferSpreadsheet works correctly.
Initially I thought that it maybe the way the Server is configured, so
we moved it to another location and tried it again with no luck. Tried
putting it back on the original server and again, it works great!
We've used SQL Compair to make sure that something didn't get changed
in the process, and everything looks ok. I will admit that there may
have been something in the way that the first server was configured,
but it doesn't explaine why the function isn't working correctly now.
All the machines that I tried it on are using the following:
Desktops:
XP Professional w/SP 1 or 2
ACCESS XP
MDAC 2.8
Servers:
Windows 2003 Server w/SP1
SQL Server 2000 w/SP4
or
SQL Server 2005 w/SP1 - We've tried both.
If anyone has any Idea's, I'm open.
Thank for your time.Hi, Dave
I have encountered the same problem in a similar configuration (Access
XP Full/Runtime, MSDE 2000 SP3/SP4, TransferDatabase function). The
issue seems to be caused by the fact that MS Access looks for a
qualified table name, using the prefix given by the username of the
current connection. In other words, if the connection is using a login
that is member of the sysadmin server role or is db_owner for the
database, then it looks for the dbo.tablename. If the login corresponds
to a particular user in that database, it looks for the
username.tablename table.
Razvan
Dave wrote:
Quote:
Originally Posted by
Ok...here's a good one.
I wrote an ADP application in ACCESS XP with a SQL 2000 back end.
Works Great! Recently we did a server migration and transfered the
database to another server. Setup all the groups and security without
a hitch. My users change the Connection to point to the new server and
all of a sudden the TransferSpreadsheet function that I'm using in two
seperate procedures starts to error saying that it can't see the table.
So I look...and there it is. The function doesn't see it though.
>
I try it on my machine and it works fine, but I have Local Admin rights
on the server though. So we start looking around. What we found is
that only those with Local Admin Rights and Server Administration
rights seem to be able to execute the procedure so that the
TransferSpreadsheet works correctly.
>
Initially I thought that it maybe the way the Server is configured, so
we moved it to another location and tried it again with no luck. Tried
putting it back on the original server and again, it works great!
We've used SQL Compair to make sure that something didn't get changed
in the process, and everything looks ok. I will admit that there may
have been something in the way that the first server was configured,
but it doesn't explaine why the function isn't working correctly now.
>
All the machines that I tried it on are using the following:
>
Desktops:
XP Professional w/SP 1 or 2
ACCESS XP
MDAC 2.8
>
Servers:
Windows 2003 Server w/SP1
SQL Server 2000 w/SP4
or
SQL Server 2005 w/SP1 - We've tried both.
>
If anyone has any Idea's, I'm open.
>
Thank for your time.
I did try including dbo.TableName. That also doesn't work. Onething
that I've been talking over with coworkers is Office Versions. The
Test User account that I used, I also used on an Office 2k3 machine.
Everyone else is either using XP or 2K. I know that that shouldn't be
an issue, but right now that's the only thing that's different.
I have to try the Test User account on an XP or 2K machine.
Razvan Socol wrote:
Quote:
Originally Posted by
Hi, Dave
>
I have encountered the same problem in a similar configuration (Access
XP Full/Runtime, MSDE 2000 SP3/SP4, TransferDatabase function). The
issue seems to be caused by the fact that MS Access looks for a
qualified table name, using the prefix given by the username of the
current connection. In other words, if the connection is using a login
that is member of the sysadmin server role or is db_owner for the
database, then it looks for the dbo.tablename. If the login corresponds
to a particular user in that database, it looks for the
username.tablename table.
>
Razvan
>
Dave wrote:
Quote:
Originally Posted by
Ok...here's a good one.
I wrote an ADP application in ACCESS XP with a SQL 2000 back end.
Works Great! Recently we did a server migration and transfered the
database to another server. Setup all the groups and security without
a hitch. My users change the Connection to point to the new server and
all of a sudden the TransferSpreadsheet function that I'm using in two
seperate procedures starts to error saying that it can't see the table.
So I look...and there it is. The function doesn't see it though.
I try it on my machine and it works fine, but I have Local Admin rights
on the server though. So we start looking around. What we found is
that only those with Local Admin Rights and Server Administration
rights seem to be able to execute the procedure so that the
TransferSpreadsheet works correctly.
Initially I thought that it maybe the way the Server is configured, so
we moved it to another location and tried it again with no luck. Tried
putting it back on the original server and again, it works great!
We've used SQL Compair to make sure that something didn't get changed
in the process, and everything looks ok. I will admit that there may
have been something in the way that the first server was configured,
but it doesn't explaine why the function isn't working correctly now.
All the machines that I tried it on are using the following:
Desktops:
XP Professional w/SP 1 or 2
ACCESS XP
MDAC 2.8
Servers:
Windows 2003 Server w/SP1
SQL Server 2000 w/SP4
or
SQL Server 2005 w/SP1 - We've tried both.
If anyone has any Idea's, I'm open.
Thank for your time.
qualified table name correctly.
i have not found a fix except to run a stored proc that changes the
object owner on the database.
Dave wrote:
Quote:
Originally Posted by
Razvan...Thanks for the reply. I must have left that part out. Sorry.
I did try including dbo.TableName. That also doesn't work. Onething
that I've been talking over with coworkers is Office Versions. The
Test User account that I used, I also used on an Office 2k3 machine.
Everyone else is either using XP or 2K. I know that that shouldn't be
an issue, but right now that's the only thing that's different.
>
I have to try the Test User account on an XP or 2K machine.
>
>
>
>
Razvan Socol wrote:
Quote:
Originally Posted by
Hi, Dave
I have encountered the same problem in a similar configuration (Access
XP Full/Runtime, MSDE 2000 SP3/SP4, TransferDatabase function). The
issue seems to be caused by the fact that MS Access looks for a
qualified table name, using the prefix given by the username of the
current connection. In other words, if the connection is using a login
that is member of the sysadmin server role or is db_owner for the
database, then it looks for the dbo.tablename. If the login corresponds
to a particular user in that database, it looks for the
username.tablename table.
Razvan
Dave wrote:
Quote:
Originally Posted by
Ok...here's a good one.
>
I wrote an ADP application in ACCESS XP with a SQL 2000 back end.
Works Great! Recently we did a server migration and transfered the
database to another server. Setup all the groups and security without
a hitch. My users change the Connection to point to the new server and
>
all of a sudden the TransferSpreadsheet function that I'm using in two
seperate procedures starts to error saying that it can't see the table.
>
So I look...and there it is. The function doesn't see it though.
>
>
I try it on my machine and it works fine, but I have Local Admin rights
>
on the server though. So we start looking around. What we found is
that only those with Local Admin Rights and Server Administration
rights seem to be able to execute the procedure so that the
TransferSpreadsheet works correctly.
>
>
Initially I thought that it maybe the way the Server is configured, so
we moved it to another location and tried it again with no luck. Tried
>
putting it back on the original server and again, it works great!
We've used SQL Compair to make sure that something didn't get changed
in the process, and everything looks ok. I will admit that there may
have been something in the way that the first server was configured,
but it doesn't explaine why the function isn't working correctly now.
>
>
All the machines that I tried it on are using the following:
>
>
Desktops:
XP Professional w/SP 1 or 2
ACCESS XP
MDAC 2.8
>
>
Servers:
Windows 2003 Server w/SP1
SQL Server 2000 w/SP4
or
SQL Server 2005 w/SP1 - We've tried both.
>
>
If anyone has any Idea's, I'm open.
>
>
Thank for your time.
If you specify dbo.TableName, then Access will dumbly look for the
table username.[dbo.TableName]. Unfortunately, it's a bug in Access,
and I agree with the workaround that mcnewsxp wrote (however, running
sp_changeobjectowner requires at least db_ddladmin and db_securityadmin
priviledges in that database).
Another workaround is to have two tables, one for each user who will be
doing the export. For example, if you want to export the data from
TableName, create a dbo.Temp table and a username.Temp table and copy
the data from TableName in both. Then execute TransferSpreadsheet and
it will get the data from one of the Temp tables, depending on the
current user. I know, it's an ugly workaround, but it works...
Razvan
Dave wrote:
Quote:
Originally Posted by
Razvan...Thanks for the reply. I must have left that part out. Sorry.
I did try including dbo.TableName. That also doesn't work. Onething
that I've been talking over with coworkers is Office Versions. The
Test User account that I used, I also used on an Office 2k3 machine.
Everyone else is either using XP or 2K. I know that that shouldn't be
an issue, but right now that's the only thing that's different.
>
I have to try the Test User account on an XP or 2K machine.
quick, so we just used SQL Reporting Services to push the export.
Thanks for everybody's help.
Razvan Socol wrote:
Quote:
Originally Posted by
Hi, Dave
>
If you specify dbo.TableName, then Access will dumbly look for the
table username.[dbo.TableName]. Unfortunately, it's a bug in Access,
and I agree with the workaround that mcnewsxp wrote (however, running
sp_changeobjectowner requires at least db_ddladmin and db_securityadmin
priviledges in that database).
>
Another workaround is to have two tables, one for each user who will be
doing the export. For example, if you want to export the data from
TableName, create a dbo.Temp table and a username.Temp table and copy
the data from TableName in both. Then execute TransferSpreadsheet and
it will get the data from one of the Temp tables, depending on the
current user. I know, it's an ugly workaround, but it works...
>
Razvan
>
Dave wrote:
Quote:
Originally Posted by
Razvan...Thanks for the reply. I must have left that part out. Sorry.
I did try including dbo.TableName. That also doesn't work. Onething
that I've been talking over with coworkers is Office Versions. The
Test User account that I used, I also used on an Office 2k3 machine.
Everyone else is either using XP or 2K. I know that that shouldn't be
an issue, but right now that's the only thing that's different.
I have to try the Test User account on an XP or 2K machine.
Access a database from a client application
Hi all...
I am writing a Windows Application which will be used by about 100 clients. (NT Authenticated)
The application is going to be used in a LAN enviroment. User who have access will download a VB.NET application from a web site.
Of course, I want to prevent any of the 100 users from using a tool such as Query Analysis or ODBC to connect directly to the database and modify data.
So, it appears the way to go is to the "Application Roles". For this to work, the application roll password has to be saved in the application to execute the sp_setapprole procedure.
Is there a way to secure this password? I am worried about a user somehow extracting the password from the application and connecting directly.
Am I on the right track here? Any ideas appreciated!
Thanks,
Forch
One method I have used is to create an admin system where you would create a Hash of the application name and version + a secret string.... Then use this hash as the password for the Application Role. Doing it this way will also make sure that the users will use the current version only..
|||Note that if the user has full control over your application and can debug it, he can break any protection scheme that you build into it. It's worth obfuscating the password, as Glenn suggested, but this protection can be broken by a knowledgeable user.
Thanks
Laurentiu
Tuesday, March 20, 2012
Access 2003 adp/proxy security - A substitute for SYSTEM_USER()
We are moving an Access2000 adp application to Access 2003. Access2000
version uses approle for security but we found Approle does not work the
same in Access2003 so we are switching to a proxy security method.
Problem
We need a way for SQL to know the nt username that initiated the proxy
connection. We need SQL to be able to retrieve that username very, very
quickly (basically without a table lookup) for each user's spids, regardless
of how many connections Access decides to make for the adp.
Details:
a.. All the insert and update triggers depended on SYSTEM_USER to stamp
the user who wrote the data on the row
b.. Many stored procedures and views use a UDF that also depends on
SYSTEM_USER
c.. Since we're reconnecting each user as a proxy user, we need a way for
SQL to determine the nt user of the application.
d.. Note: Modifying the client to pass the user in is simply not practical
due to the huge amount of code change.
Our solution
We are using SET CONTEXT_INFO to stuff the user's name into the context_info
of sysprocesses when the app first logs in. We have a UDF - fnSystemUser
that queries sysprocesses to return what SYSTEM_USER used to. Unfortunately,
for the triggers or any UDFs (which previously only hit SYSTEM_USER), that
meant a nose dive in performance since the UDF is executing on every row
instead of evaluating once as if it were deterministic for the life of that
query. Although SYSTEM_USER is nondeterministic, it appears it was only
evaluated once for the queries instead of the performance we see now on our
UDF returning the system user name from context_info, that indicates
executing on every row.
Since Access adps open multiple connections dynamically our fnSystemUser is
coded to find the Context_info of the spid that was first set through client
code. See the code below:
CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @.ContextInfo varbinary(128)
DECLARE @.DomainUserName nchar(128)
-- First attempt to get the username stuffed into
-- context_info of this connection
SELECT @.ContextInfo = context_info
FROM master..sysprocesses
WHERE spid = @.@.SPID
-- Convert it to nvarchar
SET @.DomainUserName = CAST(CAST(REPLACE(@.ContextInfo,0x0000,'') AS
varbinary(128)) AS nvarchar(50))
-- If the context info is blank then we're on one of the connections
-- that Access dynamically created but the client code can't access
to stuff
-- something into the context_info. Interrogate all other spids for
this
-- user and this client process (using net_address to guarantee we've
got the
-- right user), to pull username from the context info of another
connection/spid
IF LEN(@.DomainUserName) < 1
BEGIN
DECLARE @.NetAddress nchar(12)
SELECT TOP 1 @.NetAddress = net_address
FROM master..sysprocesses p
WHERE SPID = @.@.SPID
SELECT TOP 1 @.ContextInfo = context_info
FROM master..sysprocesses p
INNER JOIN master..sysdatabases d
on p.dbid = d.dbid
WHERE hostprocess = HOST_ID()
AND d.Name = DB_NAME()
AND NOT CAST(context_info as nchar(50))= SPACE(50)
AND (LEN(@.NetAddress) < 1 OR net_address = @.NetAddress)
ORDER BY Context_info
SET @.DomainUserName =
CAST(CAST(REPLACE(@.ContextInfo,0x0000,'') AS varbinary(128)) AS
nvarchar(50))
END
/* Fail safe, if we couldn't find a non-empty Context_Info to
discover the
logged in user then could be the user is attached with Query
Analyzer or
some other method and we'll just use the user as they are logged
into SQL */
IF LEN(@.DomainUserName) < 1
SET @.DomainUserName = SYSTEM_USER
RETURN
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN
ame)-(CHARINDEX(N'\',@.DomainUserName)))
END
GO
Solving the performance problem
Here are some workaround we've tried:
a.. Querying sysprocesses seems slow so we instead created a table
SyUserLogin and use HostID() and HostName() to find the right row. We've
seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2 users
have the same HostName. So there is no guarantee that when a user logs in
that HostID() and HostName() will uniquely identify each user. but the odds
are in our favor.
Basically here's what runs (stripped of the uniqueness checks) when the
user first logs in to SQL:
INSERT INTO [dbo].[SyUserLogin]
([UserName], [HostName], [HostID], [NetAddress])
SELECT @.SystemUserName, -- username that we pass in
Host_Name(),
Host_id(),
@.NetAddress -- from the sysprocesses row for this spid
Here's the new fnSystemUser:
CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
RETURN
(SELECT UpdateUserName
FROM SyUserLogin
WHERE HostName = Host_NAME()
AND HostID = HOST_ID()
)
END
a.. We tried to change the connection string to use either "Application
Name" or "Workstation ID" as a cubby hole to stuff the nt user name. We can
get this to work in a VB Script:
dim objConnection
dim strAccessConnect
strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer" & _
";Initial Catalog=MyDatabase" & _
";Persist Security Info=False;Application
Name=MyTestApp;Workstation ID=NTUserName;"
Set objConnection = createobject("ADODB.Connection")
objConnection.Open strAccessConnect, "OurProxy", "ProxyPassword"
Msgbox objConnection.ConnectionString
objconnection.close
set objconnection = nothing
But when we try this in Access2003, Access seems to stomp on those value
when it connects. We've played with the Connection dialog settings, but
can't seem to convince Access to leave our connection string values alone.
If we could get this to work then it would be ideal. Querying HostName() or
even Appname() although obscure would be as fast as SYSTEM_USER()
a.. Could we somehow create a function that would wrap context_info and
match the speed of SYSTEM_USER?
Thank you in advance for reading through this and commenting!
Kimberley Yochum kyochum@.wthq.com
i dont want to sound dumb, but you've tried SUSER_SNAME() and all the other
ones, right?
also, if you're scanning against a large list of Text data, you might want
to consider using HASH INDEXES (aka the checksum function) in order to speed
this.
"Kimberley Yochum" <kyochum@.bigzoo.net> wrote in message
news:%23BOvPMX0EHA.632@.TK2MSFTNGP10.phx.gbl...
> We are moving an Access2000 adp application to Access 2003. Access2000
> version uses approle for security but we found Approle does not work the
> same in Access2003 so we are switching to a proxy security method.
> Problem
> We need a way for SQL to know the nt username that initiated the proxy
> connection. We need SQL to be able to retrieve that username very, very
> quickly (basically without a table lookup) for each user's spids,
regardless
> of how many connections Access decides to make for the adp.
> Details:
> a.. All the insert and update triggers depended on SYSTEM_USER to stamp
> the user who wrote the data on the row
> b.. Many stored procedures and views use a UDF that also depends on
> SYSTEM_USER
> c.. Since we're reconnecting each user as a proxy user, we need a way
for
> SQL to determine the nt user of the application.
> d.. Note: Modifying the client to pass the user in is simply not
practical
> due to the huge amount of code change.
> Our solution
> We are using SET CONTEXT_INFO to stuff the user's name into the
context_info
> of sysprocesses when the app first logs in. We have a UDF - fnSystemUser
> that queries sysprocesses to return what SYSTEM_USER used to.
Unfortunately,
> for the triggers or any UDFs (which previously only hit SYSTEM_USER), that
> meant a nose dive in performance since the UDF is executing on every row
> instead of evaluating once as if it were deterministic for the life of
that
> query. Although SYSTEM_USER is nondeterministic, it appears it was only
> evaluated once for the queries instead of the performance we see now on
our
> UDF returning the system user name from context_info, that indicates
> executing on every row.
> Since Access adps open multiple connections dynamically our fnSystemUser
is
> coded to find the Context_info of the spid that was first set through
client
> code. See the code below:
>
> CREATE FUNCTION dbo.fnSystemUser()
> RETURNS nvarchar(50)
> AS
> BEGIN
> DECLARE @.ContextInfo varbinary(128)
> DECLARE @.DomainUserName nchar(128)
> -- First attempt to get the username stuffed into
> -- context_info of this connection
> SELECT @.ContextInfo = context_info
> FROM master..sysprocesses
> WHERE spid = @.@.SPID
> -- Convert it to nvarchar
> SET @.DomainUserName = CAST(CAST(REPLACE(@.ContextInfo,0x0000,'') AS
> varbinary(128)) AS nvarchar(50))
> -- If the context info is blank then we're on one of the
connections
> -- that Access dynamically created but the client code can't access
> to stuff
> -- something into the context_info. Interrogate all other spids
for
> this
> -- user and this client process (using net_address to guarantee
we've
> got the
> -- right user), to pull username from the context info of another
> connection/spid
> IF LEN(@.DomainUserName) < 1
> BEGIN
> DECLARE @.NetAddress nchar(12)
> SELECT TOP 1 @.NetAddress = net_address
> FROM master..sysprocesses p
> WHERE SPID = @.@.SPID
> SELECT TOP 1 @.ContextInfo = context_info
> FROM master..sysprocesses p
> INNER JOIN master..sysdatabases d
> on p.dbid = d.dbid
> WHERE hostprocess = HOST_ID()
> AND d.Name = DB_NAME()
> AND NOT CAST(context_info as nchar(50))= SPACE(50)
> AND (LEN(@.NetAddress) < 1 OR net_address = @.NetAddress)
> ORDER BY Context_info
> SET @.DomainUserName =
> CAST(CAST(REPLACE(@.ContextInfo,0x0000,'') AS varbinary(128)) AS
> nvarchar(50))
> END
> /* Fail safe, if we couldn't find a non-empty Context_Info to
> discover the
> logged in user then could be the user is attached with Query
> Analyzer or
> some other method and we'll just use the user as they are logged
> into SQL */
> IF LEN(@.DomainUserName) < 1
> SET @.DomainUserName = SYSTEM_USER
> RETURN
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN
> ame)-(CHARINDEX(N'\',@.DomainUserName)))
> END
> GO
> Solving the performance problem
> Here are some workaround we've tried:
> a.. Querying sysprocesses seems slow so we instead created a table
> SyUserLogin and use HostID() and HostName() to find the right row. We've
> seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2 users
> have the same HostName. So there is no guarantee that when a user logs in
> that HostID() and HostName() will uniquely identify each user. but the
odds
> are in our favor.
> Basically here's what runs (stripped of the uniqueness checks) when the
> user first logs in to SQL:
> INSERT INTO [dbo].[SyUserLogin]
> ([UserName], [HostName], [HostID], [NetAddress])
> SELECT @.SystemUserName, -- username that we pass in
> Host_Name(),
> Host_id(),
> @.NetAddress -- from the sysprocesses row for this spid
> Here's the new fnSystemUser:
> CREATE FUNCTION dbo.fnSystemUser()
> RETURNS nvarchar(50)
> AS
> BEGIN
> RETURN
> (SELECT UpdateUserName
> FROM SyUserLogin
> WHERE HostName = Host_NAME()
> AND HostID = HOST_ID()
> )
> END
>
> a.. We tried to change the connection string to use either "Application
> Name" or "Workstation ID" as a cubby hole to stuff the nt user name. We
can
> get this to work in a VB Script:
> dim objConnection
> dim strAccessConnect
> strAccessConnect = "Provider=SQLOLEDB.1" & _
> ";Net=dbnmpntw;Data Source=ASQLServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Persist Security Info=False;Application
> Name=MyTestApp;Workstation ID=NTUserName;"
> Set objConnection = createobject("ADODB.Connection")
> objConnection.Open strAccessConnect, "OurProxy", "ProxyPassword"
> Msgbox objConnection.ConnectionString
> objconnection.close
> set objconnection = nothing
>
> But when we try this in Access2003, Access seems to stomp on those value
> when it connects. We've played with the Connection dialog settings, but
> can't seem to convince Access to leave our connection string values alone.
> If we could get this to work then it would be ideal. Querying HostName()
or
> even Appname() although obscure would be as fast as SYSTEM_USER()
> a.. Could we somehow create a function that would wrap context_info and
> match the speed of SYSTEM_USER?
> Thank you in advance for reading through this and commenting!
> Kimberley Yochum kyochum@.wthq.com
>
>
|||Thank you Aaron, but all the user functions (System_user, current_user,
session_user, user, user_name, suser_sname, etc.) return the Proxy user name
once a SQL user login has been performed.
Still hoping for some understanding about what Access is doing to my
connection string values or suggestions on what we can do in SQL...
Thank you in advance.
"aaron kempf" <aarkem@.safeco.com> wrote in message
news:OF9GDhZ0EHA.2228@.TK2MSFTNGP15.phx.gbl...
> i dont want to sound dumb, but you've tried SUSER_SNAME() and all the
other
> ones, right?
> also, if you're scanning against a large list of Text data, you might want
> to consider using HASH INDEXES (aka the checksum function) in order to
speed[vbcol=seagreen]
> this.
>
>
> "Kimberley Yochum" <kyochum@.bigzoo.net> wrote in message
> news:%23BOvPMX0EHA.632@.TK2MSFTNGP10.phx.gbl...
> regardless
stamp[vbcol=seagreen]
> for
> practical
> context_info
> Unfortunately,
that[vbcol=seagreen]
> that
> our
> is
> client
AS[vbcol=seagreen]
> connections
access[vbcol=seagreen]
> for
> we've
logged
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN[vbcol=seagreen]
users[vbcol=seagreen]
in[vbcol=seagreen]
> odds
the[vbcol=seagreen]
"Application[vbcol=seagreen]
> can
alone.[vbcol=seagreen]
> or
and
>
|||can you use the application name value instead?
"Kimberley Yochum" <kyochum@.wthq.com> wrote in message
news:%23rCee%23Z0EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Thank you Aaron, but all the user functions (System_user, current_user,
> session_user, user, user_name, suser_sname, etc.) return the Proxy user
name[vbcol=seagreen]
> once a SQL user login has been performed.
> Still hoping for some understanding about what Access is doing to my
> connection string values or suggestions on what we can do in SQL...
> Thank you in advance.
> "aaron kempf" <aarkem@.safeco.com> wrote in message
> news:OF9GDhZ0EHA.2228@.TK2MSFTNGP15.phx.gbl...
> other
want[vbcol=seagreen]
> speed
the[vbcol=seagreen]
very[vbcol=seagreen]
> stamp
way[vbcol=seagreen]
fnSystemUser[vbcol=seagreen]
> that
row[vbcol=seagreen]
only[vbcol=seagreen]
on[vbcol=seagreen]
fnSystemUser[vbcol=seagreen]
> AS
> access
spids[vbcol=seagreen]
another[vbcol=seagreen]
@.NetAddress)
> logged
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN[vbcol=seagreen]
We've[vbcol=seagreen]
> users
> in
> the
> "Application
We[vbcol=seagreen]
_[vbcol=seagreen]
value[vbcol=seagreen]
but[vbcol=seagreen]
> alone.
HostName()
> and
>
|||Yes I would love to but all my attempts to set an App name in the connection
string are being overwritten by Access.
Here's the connection string I tried. When I tried this in a VB script, it
worked fine and sysprocess showed the appname "MyTestApp" and the hostname
"NTUserName". But when I programmatically set the Access
CurrentProject.Connection by opening with the same connection string, those
value get tossed.
strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer"
& _
";Initial Catalog=MyDatabase" & _
";Persist Security
Info=False;Application Name=MyTestApp;Workstation ID=NTUserName;"
Any suggestions? Have you had any success in setting this before? I'd love
to hear about it!!
"david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
news:%23sSxhz30EHA.1740@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> can you use the application name value instead?
> "Kimberley Yochum" <kyochum@.wthq.com> wrote in message
> news:%23rCee%23Z0EHA.1404@.TK2MSFTNGP11.phx.gbl...
> name
> want
Access2000[vbcol=seagreen]
> the
proxy[vbcol=seagreen]
> very
on[vbcol=seagreen]
> way
> fnSystemUser
SYSTEM_USER),[vbcol=seagreen]
> row
of[vbcol=seagreen]
> only
> on
> fnSystemUser
through[vbcol=seagreen]
CAST(CAST(REPLACE(@.ContextInfo,0x0000,'')[vbcol=seagreen]
> spids
guarantee[vbcol=seagreen]
> another
> @.NetAddress)
Query
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN[vbcol=seagreen]
> We've
logs[vbcol=seagreen]
the[vbcol=seagreen]
when[vbcol=seagreen]
> We
&[vbcol=seagreen]
> _
Info=False;Application[vbcol=seagreen]
> value
> but
> HostName()
context_info
>
|||> string are being overwritten by Access.
rats. :~(
I'm an Access person, and I don't know anything else you could
try on the Access side. I guess you will be looking for a way
to cache the value, so that fnSystemUser doesn't need to do
SELECT query on every call, but I don't know what the options
are in SQL Server.
(david)
"Kimberley Yochum" <kyochum@.wthq.com> wrote in message
news:OhSjrRA1EHA.3448@.TK2MSFTNGP09.phx.gbl...
> Yes I would love to but all my attempts to set an App name in the
connection
> string are being overwritten by Access.
> Here's the connection string I tried. When I tried this in a VB script,
it
> worked fine and sysprocess showed the appname "MyTestApp" and the hostname
> "NTUserName". But when I programmatically set the Access
> CurrentProject.Connection by opening with the same connection string,
those
> value get tossed.
> strAccessConnect = "Provider=SQLOLEDB.1" & _
> ";Net=dbnmpntw;Data
Source=ASQLServer"
> & _
> ";Initial Catalog=MyDatabase" & _
> ";Persist Security
> Info=False;Application Name=MyTestApp;Workstation ID=NTUserName;"
>
> Any suggestions? Have you had any success in setting this before? I'd
love[vbcol=seagreen]
> to hear about it!!
> "david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
> news:%23sSxhz30EHA.1740@.TK2MSFTNGP15.phx.gbl...
current_user,[vbcol=seagreen]
user[vbcol=seagreen]
the[vbcol=seagreen]
might[vbcol=seagreen]
to[vbcol=seagreen]
> Access2000
work[vbcol=seagreen]
> proxy
to[vbcol=seagreen]
> on
a[vbcol=seagreen]
> SYSTEM_USER),
every[vbcol=seagreen]
life[vbcol=seagreen]
> of
now[vbcol=seagreen]
indicates[vbcol=seagreen]
> through
> CAST(CAST(REPLACE(@.ContextInfo,0x0000,'')
can't[vbcol=seagreen]
> guarantee
SPACE(50)[vbcol=seagreen]
to[vbcol=seagreen]
> Query
are
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN[vbcol=seagreen]
table[vbcol=seagreen]
2[vbcol=seagreen]
> logs
> the
> when
name.[vbcol=seagreen]
Source=ASQLServer"[vbcol=seagreen]
> &
> Info=False;Application
settings,[vbcol=seagreen]
values
> context_info
>
|||try with current_user() or user_name()
"david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
news:OHiFPga1EHA.2568@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> rats. :~(
> I'm an Access person, and I don't know anything else you could
> try on the Access side. I guess you will be looking for a way
> to cache the value, so that fnSystemUser doesn't need to do
> SELECT query on every call, but I don't know what the options
> are in SQL Server.
> (david)
>
> "Kimberley Yochum" <kyochum@.wthq.com> wrote in message
> news:OhSjrRA1EHA.3448@.TK2MSFTNGP09.phx.gbl...
> connection
> it
hostname[vbcol=seagreen]
> those
> Source=ASQLServer"
> love
> current_user,
> user
> the
> might
order[vbcol=seagreen]
> to
> work
method.[vbcol=seagreen]
very,[vbcol=seagreen]
spids,[vbcol=seagreen]
> to
depends[vbcol=seagreen]
need[vbcol=seagreen]
> a
not[vbcol=seagreen]
> every
> life
was[vbcol=seagreen]
> now
> indicates
> can't
other[vbcol=seagreen]
> SPACE(50)
Context_Info
> to
> are
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN[vbcol=seagreen]
> table
row.[vbcol=seagreen]
seen[vbcol=seagreen]
> 2
user[vbcol=seagreen]
but[vbcol=seagreen]
spid[vbcol=seagreen]
> name.
> Source=ASQLServer"
those[vbcol=seagreen]
> settings,
> values
SYSTEM_USER()
>
version uses approle for security but we found Approle does not work the
same in Access2003 so we are switching to a proxy security method.
Problem
We need a way for SQL to know the nt username that initiated the proxy
connection. We need SQL to be able to retrieve that username very, very
quickly (basically without a table lookup) for each user's spids, regardless
of how many connections Access decides to make for the adp.
Details:
a.. All the insert and update triggers depended on SYSTEM_USER to stamp
the user who wrote the data on the row
b.. Many stored procedures and views use a UDF that also depends on
SYSTEM_USER
c.. Since we're reconnecting each user as a proxy user, we need a way for
SQL to determine the nt user of the application.
d.. Note: Modifying the client to pass the user in is simply not practical
due to the huge amount of code change.
Our solution
We are using SET CONTEXT_INFO to stuff the user's name into the context_info
of sysprocesses when the app first logs in. We have a UDF - fnSystemUser
that queries sysprocesses to return what SYSTEM_USER used to. Unfortunately,
for the triggers or any UDFs (which previously only hit SYSTEM_USER), that
meant a nose dive in performance since the UDF is executing on every row
instead of evaluating once as if it were deterministic for the life of that
query. Although SYSTEM_USER is nondeterministic, it appears it was only
evaluated once for the queries instead of the performance we see now on our
UDF returning the system user name from context_info, that indicates
executing on every row.
Since Access adps open multiple connections dynamically our fnSystemUser is
coded to find the Context_info of the spid that was first set through client
code. See the code below:
CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @.ContextInfo varbinary(128)
DECLARE @.DomainUserName nchar(128)
-- First attempt to get the username stuffed into
-- context_info of this connection
SELECT @.ContextInfo = context_info
FROM master..sysprocesses
WHERE spid = @.@.SPID
-- Convert it to nvarchar
SET @.DomainUserName = CAST(CAST(REPLACE(@.ContextInfo,0x0000,'') AS
varbinary(128)) AS nvarchar(50))
-- If the context info is blank then we're on one of the connections
-- that Access dynamically created but the client code can't access
to stuff
-- something into the context_info. Interrogate all other spids for
this
-- user and this client process (using net_address to guarantee we've
got the
-- right user), to pull username from the context info of another
connection/spid
IF LEN(@.DomainUserName) < 1
BEGIN
DECLARE @.NetAddress nchar(12)
SELECT TOP 1 @.NetAddress = net_address
FROM master..sysprocesses p
WHERE SPID = @.@.SPID
SELECT TOP 1 @.ContextInfo = context_info
FROM master..sysprocesses p
INNER JOIN master..sysdatabases d
on p.dbid = d.dbid
WHERE hostprocess = HOST_ID()
AND d.Name = DB_NAME()
AND NOT CAST(context_info as nchar(50))= SPACE(50)
AND (LEN(@.NetAddress) < 1 OR net_address = @.NetAddress)
ORDER BY Context_info
SET @.DomainUserName =
CAST(CAST(REPLACE(@.ContextInfo,0x0000,'') AS varbinary(128)) AS
nvarchar(50))
END
/* Fail safe, if we couldn't find a non-empty Context_Info to
discover the
logged in user then could be the user is attached with Query
Analyzer or
some other method and we'll just use the user as they are logged
into SQL */
IF LEN(@.DomainUserName) < 1
SET @.DomainUserName = SYSTEM_USER
RETURN
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN
ame)-(CHARINDEX(N'\',@.DomainUserName)))
END
GO
Solving the performance problem
Here are some workaround we've tried:
a.. Querying sysprocesses seems slow so we instead created a table
SyUserLogin and use HostID() and HostName() to find the right row. We've
seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2 users
have the same HostName. So there is no guarantee that when a user logs in
that HostID() and HostName() will uniquely identify each user. but the odds
are in our favor.
Basically here's what runs (stripped of the uniqueness checks) when the
user first logs in to SQL:
INSERT INTO [dbo].[SyUserLogin]
([UserName], [HostName], [HostID], [NetAddress])
SELECT @.SystemUserName, -- username that we pass in
Host_Name(),
Host_id(),
@.NetAddress -- from the sysprocesses row for this spid
Here's the new fnSystemUser:
CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
RETURN
(SELECT UpdateUserName
FROM SyUserLogin
WHERE HostName = Host_NAME()
AND HostID = HOST_ID()
)
END
a.. We tried to change the connection string to use either "Application
Name" or "Workstation ID" as a cubby hole to stuff the nt user name. We can
get this to work in a VB Script:
dim objConnection
dim strAccessConnect
strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer" & _
";Initial Catalog=MyDatabase" & _
";Persist Security Info=False;Application
Name=MyTestApp;Workstation ID=NTUserName;"
Set objConnection = createobject("ADODB.Connection")
objConnection.Open strAccessConnect, "OurProxy", "ProxyPassword"
Msgbox objConnection.ConnectionString
objconnection.close
set objconnection = nothing
But when we try this in Access2003, Access seems to stomp on those value
when it connects. We've played with the Connection dialog settings, but
can't seem to convince Access to leave our connection string values alone.
If we could get this to work then it would be ideal. Querying HostName() or
even Appname() although obscure would be as fast as SYSTEM_USER()
a.. Could we somehow create a function that would wrap context_info and
match the speed of SYSTEM_USER?
Thank you in advance for reading through this and commenting!
Kimberley Yochum kyochum@.wthq.com
i dont want to sound dumb, but you've tried SUSER_SNAME() and all the other
ones, right?
also, if you're scanning against a large list of Text data, you might want
to consider using HASH INDEXES (aka the checksum function) in order to speed
this.
"Kimberley Yochum" <kyochum@.bigzoo.net> wrote in message
news:%23BOvPMX0EHA.632@.TK2MSFTNGP10.phx.gbl...
> We are moving an Access2000 adp application to Access 2003. Access2000
> version uses approle for security but we found Approle does not work the
> same in Access2003 so we are switching to a proxy security method.
> Problem
> We need a way for SQL to know the nt username that initiated the proxy
> connection. We need SQL to be able to retrieve that username very, very
> quickly (basically without a table lookup) for each user's spids,
regardless
> of how many connections Access decides to make for the adp.
> Details:
> a.. All the insert and update triggers depended on SYSTEM_USER to stamp
> the user who wrote the data on the row
> b.. Many stored procedures and views use a UDF that also depends on
> SYSTEM_USER
> c.. Since we're reconnecting each user as a proxy user, we need a way
for
> SQL to determine the nt user of the application.
> d.. Note: Modifying the client to pass the user in is simply not
practical
> due to the huge amount of code change.
> Our solution
> We are using SET CONTEXT_INFO to stuff the user's name into the
context_info
> of sysprocesses when the app first logs in. We have a UDF - fnSystemUser
> that queries sysprocesses to return what SYSTEM_USER used to.
Unfortunately,
> for the triggers or any UDFs (which previously only hit SYSTEM_USER), that
> meant a nose dive in performance since the UDF is executing on every row
> instead of evaluating once as if it were deterministic for the life of
that
> query. Although SYSTEM_USER is nondeterministic, it appears it was only
> evaluated once for the queries instead of the performance we see now on
our
> UDF returning the system user name from context_info, that indicates
> executing on every row.
> Since Access adps open multiple connections dynamically our fnSystemUser
is
> coded to find the Context_info of the spid that was first set through
client
> code. See the code below:
>
> CREATE FUNCTION dbo.fnSystemUser()
> RETURNS nvarchar(50)
> AS
> BEGIN
> DECLARE @.ContextInfo varbinary(128)
> DECLARE @.DomainUserName nchar(128)
> -- First attempt to get the username stuffed into
> -- context_info of this connection
> SELECT @.ContextInfo = context_info
> FROM master..sysprocesses
> WHERE spid = @.@.SPID
> -- Convert it to nvarchar
> SET @.DomainUserName = CAST(CAST(REPLACE(@.ContextInfo,0x0000,'') AS
> varbinary(128)) AS nvarchar(50))
> -- If the context info is blank then we're on one of the
connections
> -- that Access dynamically created but the client code can't access
> to stuff
> -- something into the context_info. Interrogate all other spids
for
> this
> -- user and this client process (using net_address to guarantee
we've
> got the
> -- right user), to pull username from the context info of another
> connection/spid
> IF LEN(@.DomainUserName) < 1
> BEGIN
> DECLARE @.NetAddress nchar(12)
> SELECT TOP 1 @.NetAddress = net_address
> FROM master..sysprocesses p
> WHERE SPID = @.@.SPID
> SELECT TOP 1 @.ContextInfo = context_info
> FROM master..sysprocesses p
> INNER JOIN master..sysdatabases d
> on p.dbid = d.dbid
> WHERE hostprocess = HOST_ID()
> AND d.Name = DB_NAME()
> AND NOT CAST(context_info as nchar(50))= SPACE(50)
> AND (LEN(@.NetAddress) < 1 OR net_address = @.NetAddress)
> ORDER BY Context_info
> SET @.DomainUserName =
> CAST(CAST(REPLACE(@.ContextInfo,0x0000,'') AS varbinary(128)) AS
> nvarchar(50))
> END
> /* Fail safe, if we couldn't find a non-empty Context_Info to
> discover the
> logged in user then could be the user is attached with Query
> Analyzer or
> some other method and we'll just use the user as they are logged
> into SQL */
> IF LEN(@.DomainUserName) < 1
> SET @.DomainUserName = SYSTEM_USER
> RETURN
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN
> ame)-(CHARINDEX(N'\',@.DomainUserName)))
> END
> GO
> Solving the performance problem
> Here are some workaround we've tried:
> a.. Querying sysprocesses seems slow so we instead created a table
> SyUserLogin and use HostID() and HostName() to find the right row. We've
> seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2 users
> have the same HostName. So there is no guarantee that when a user logs in
> that HostID() and HostName() will uniquely identify each user. but the
odds
> are in our favor.
> Basically here's what runs (stripped of the uniqueness checks) when the
> user first logs in to SQL:
> INSERT INTO [dbo].[SyUserLogin]
> ([UserName], [HostName], [HostID], [NetAddress])
> SELECT @.SystemUserName, -- username that we pass in
> Host_Name(),
> Host_id(),
> @.NetAddress -- from the sysprocesses row for this spid
> Here's the new fnSystemUser:
> CREATE FUNCTION dbo.fnSystemUser()
> RETURNS nvarchar(50)
> AS
> BEGIN
> RETURN
> (SELECT UpdateUserName
> FROM SyUserLogin
> WHERE HostName = Host_NAME()
> AND HostID = HOST_ID()
> )
> END
>
> a.. We tried to change the connection string to use either "Application
> Name" or "Workstation ID" as a cubby hole to stuff the nt user name. We
can
> get this to work in a VB Script:
> dim objConnection
> dim strAccessConnect
> strAccessConnect = "Provider=SQLOLEDB.1" & _
> ";Net=dbnmpntw;Data Source=ASQLServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Persist Security Info=False;Application
> Name=MyTestApp;Workstation ID=NTUserName;"
> Set objConnection = createobject("ADODB.Connection")
> objConnection.Open strAccessConnect, "OurProxy", "ProxyPassword"
> Msgbox objConnection.ConnectionString
> objconnection.close
> set objconnection = nothing
>
> But when we try this in Access2003, Access seems to stomp on those value
> when it connects. We've played with the Connection dialog settings, but
> can't seem to convince Access to leave our connection string values alone.
> If we could get this to work then it would be ideal. Querying HostName()
or
> even Appname() although obscure would be as fast as SYSTEM_USER()
> a.. Could we somehow create a function that would wrap context_info and
> match the speed of SYSTEM_USER?
> Thank you in advance for reading through this and commenting!
> Kimberley Yochum kyochum@.wthq.com
>
>
|||Thank you Aaron, but all the user functions (System_user, current_user,
session_user, user, user_name, suser_sname, etc.) return the Proxy user name
once a SQL user login has been performed.
Still hoping for some understanding about what Access is doing to my
connection string values or suggestions on what we can do in SQL...
Thank you in advance.
"aaron kempf" <aarkem@.safeco.com> wrote in message
news:OF9GDhZ0EHA.2228@.TK2MSFTNGP15.phx.gbl...
> i dont want to sound dumb, but you've tried SUSER_SNAME() and all the
other
> ones, right?
> also, if you're scanning against a large list of Text data, you might want
> to consider using HASH INDEXES (aka the checksum function) in order to
speed[vbcol=seagreen]
> this.
>
>
> "Kimberley Yochum" <kyochum@.bigzoo.net> wrote in message
> news:%23BOvPMX0EHA.632@.TK2MSFTNGP10.phx.gbl...
> regardless
stamp[vbcol=seagreen]
> for
> practical
> context_info
> Unfortunately,
that[vbcol=seagreen]
> that
> our
> is
> client
AS[vbcol=seagreen]
> connections
access[vbcol=seagreen]
> for
> we've
logged
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN[vbcol=seagreen]
users[vbcol=seagreen]
in[vbcol=seagreen]
> odds
the[vbcol=seagreen]
"Application[vbcol=seagreen]
> can
alone.[vbcol=seagreen]
> or
and
>
|||can you use the application name value instead?
"Kimberley Yochum" <kyochum@.wthq.com> wrote in message
news:%23rCee%23Z0EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Thank you Aaron, but all the user functions (System_user, current_user,
> session_user, user, user_name, suser_sname, etc.) return the Proxy user
name[vbcol=seagreen]
> once a SQL user login has been performed.
> Still hoping for some understanding about what Access is doing to my
> connection string values or suggestions on what we can do in SQL...
> Thank you in advance.
> "aaron kempf" <aarkem@.safeco.com> wrote in message
> news:OF9GDhZ0EHA.2228@.TK2MSFTNGP15.phx.gbl...
> other
want[vbcol=seagreen]
> speed
the[vbcol=seagreen]
very[vbcol=seagreen]
> stamp
way[vbcol=seagreen]
fnSystemUser[vbcol=seagreen]
> that
row[vbcol=seagreen]
only[vbcol=seagreen]
on[vbcol=seagreen]
fnSystemUser[vbcol=seagreen]
> AS
> access
spids[vbcol=seagreen]
another[vbcol=seagreen]
@.NetAddress)
> logged
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN[vbcol=seagreen]
We've[vbcol=seagreen]
> users
> in
> the
> "Application
We[vbcol=seagreen]
_[vbcol=seagreen]
value[vbcol=seagreen]
but[vbcol=seagreen]
> alone.
HostName()
> and
>
|||Yes I would love to but all my attempts to set an App name in the connection
string are being overwritten by Access.
Here's the connection string I tried. When I tried this in a VB script, it
worked fine and sysprocess showed the appname "MyTestApp" and the hostname
"NTUserName". But when I programmatically set the Access
CurrentProject.Connection by opening with the same connection string, those
value get tossed.
strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer"
& _
";Initial Catalog=MyDatabase" & _
";Persist Security
Info=False;Application Name=MyTestApp;Workstation ID=NTUserName;"
Any suggestions? Have you had any success in setting this before? I'd love
to hear about it!!
"david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
news:%23sSxhz30EHA.1740@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> can you use the application name value instead?
> "Kimberley Yochum" <kyochum@.wthq.com> wrote in message
> news:%23rCee%23Z0EHA.1404@.TK2MSFTNGP11.phx.gbl...
> name
> want
Access2000[vbcol=seagreen]
> the
proxy[vbcol=seagreen]
> very
on[vbcol=seagreen]
> way
> fnSystemUser
SYSTEM_USER),[vbcol=seagreen]
> row
of[vbcol=seagreen]
> only
> on
> fnSystemUser
through[vbcol=seagreen]
CAST(CAST(REPLACE(@.ContextInfo,0x0000,'')[vbcol=seagreen]
> spids
guarantee[vbcol=seagreen]
> another
> @.NetAddress)
Query
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN[vbcol=seagreen]
> We've
logs[vbcol=seagreen]
the[vbcol=seagreen]
when[vbcol=seagreen]
> We
&[vbcol=seagreen]
> _
Info=False;Application[vbcol=seagreen]
> value
> but
> HostName()
context_info
>
|||> string are being overwritten by Access.
rats. :~(
I'm an Access person, and I don't know anything else you could
try on the Access side. I guess you will be looking for a way
to cache the value, so that fnSystemUser doesn't need to do
SELECT query on every call, but I don't know what the options
are in SQL Server.
(david)
"Kimberley Yochum" <kyochum@.wthq.com> wrote in message
news:OhSjrRA1EHA.3448@.TK2MSFTNGP09.phx.gbl...
> Yes I would love to but all my attempts to set an App name in the
connection
> string are being overwritten by Access.
> Here's the connection string I tried. When I tried this in a VB script,
it
> worked fine and sysprocess showed the appname "MyTestApp" and the hostname
> "NTUserName". But when I programmatically set the Access
> CurrentProject.Connection by opening with the same connection string,
those
> value get tossed.
> strAccessConnect = "Provider=SQLOLEDB.1" & _
> ";Net=dbnmpntw;Data
Source=ASQLServer"
> & _
> ";Initial Catalog=MyDatabase" & _
> ";Persist Security
> Info=False;Application Name=MyTestApp;Workstation ID=NTUserName;"
>
> Any suggestions? Have you had any success in setting this before? I'd
love[vbcol=seagreen]
> to hear about it!!
> "david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
> news:%23sSxhz30EHA.1740@.TK2MSFTNGP15.phx.gbl...
current_user,[vbcol=seagreen]
user[vbcol=seagreen]
the[vbcol=seagreen]
might[vbcol=seagreen]
to[vbcol=seagreen]
> Access2000
work[vbcol=seagreen]
> proxy
to[vbcol=seagreen]
> on
a[vbcol=seagreen]
> SYSTEM_USER),
every[vbcol=seagreen]
life[vbcol=seagreen]
> of
now[vbcol=seagreen]
indicates[vbcol=seagreen]
> through
> CAST(CAST(REPLACE(@.ContextInfo,0x0000,'')
can't[vbcol=seagreen]
> guarantee
SPACE(50)[vbcol=seagreen]
to[vbcol=seagreen]
> Query
are
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN[vbcol=seagreen]
table[vbcol=seagreen]
2[vbcol=seagreen]
> logs
> the
> when
name.[vbcol=seagreen]
Source=ASQLServer"[vbcol=seagreen]
> &
> Info=False;Application
settings,[vbcol=seagreen]
values
> context_info
>
|||try with current_user() or user_name()
"david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
news:OHiFPga1EHA.2568@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> rats. :~(
> I'm an Access person, and I don't know anything else you could
> try on the Access side. I guess you will be looking for a way
> to cache the value, so that fnSystemUser doesn't need to do
> SELECT query on every call, but I don't know what the options
> are in SQL Server.
> (david)
>
> "Kimberley Yochum" <kyochum@.wthq.com> wrote in message
> news:OhSjrRA1EHA.3448@.TK2MSFTNGP09.phx.gbl...
> connection
> it
hostname[vbcol=seagreen]
> those
> Source=ASQLServer"
> love
> current_user,
> user
> the
> might
order[vbcol=seagreen]
> to
> work
method.[vbcol=seagreen]
very,[vbcol=seagreen]
spids,[vbcol=seagreen]
> to
depends[vbcol=seagreen]
need[vbcol=seagreen]
> a
not[vbcol=seagreen]
> every
> life
was[vbcol=seagreen]
> now
> indicates
> can't
other[vbcol=seagreen]
> SPACE(50)
Context_Info
> to
> are
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN[vbcol=seagreen]
> table
row.[vbcol=seagreen]
seen[vbcol=seagreen]
> 2
user[vbcol=seagreen]
but[vbcol=seagreen]
spid[vbcol=seagreen]
> name.
> Source=ASQLServer"
those[vbcol=seagreen]
> settings,
> values
SYSTEM_USER()
>
Access 2003 adp/proxy security - A substitute for SYSTEM_USER()
We are moving an Access2000 adp application to Access 2003. Access2000
version uses approle for security but we found Approle does not work the
same in Access2003 so we are switching to a proxy security method.
Problem
We need a way for SQL to know the nt username that initiated the proxy
connection. We need SQL to be able to retrieve that username very, very
quickly (basically without a table lookup) for each user's spids, regardless
of how many connections Access decides to make for the adp.
Details:
a.. All the insert and update triggers depended on SYSTEM_USER to stamp
the user who wrote the data on the row
b.. Many stored procedures and views use a UDF that also depends on
SYSTEM_USER
c.. Since we're reconnecting each user as a proxy user, we need a way for
SQL to determine the nt user of the application.
d.. Note: Modifying the client to pass the user in is simply not practical
due to the huge amount of code change.
Our solution
We are using SET CONTEXT_INFO to stuff the user's name into the context_info
of sysprocesses when the app first logs in. We have a UDF - fnSystemUser
that queries sysprocesses to return what SYSTEM_USER used to. Unfortunately,
for the triggers or any UDFs (which previously only hit SYSTEM_USER), that
meant a nose dive in performance since the UDF is executing on every row
instead of evaluating once as if it were deterministic for the life of that
query. Although SYSTEM_USER is nondeterministic, it appears it was only
evaluated once for the queries instead of the performance we see now on our
UDF returning the system user name from context_info, that indicates
executing on every row.
Since Access adps open multiple connections dynamically our fnSystemUser is
coded to find the Context_info of the spid that was first set through client
code. See the code below:
CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @.ContextInfo varbinary(128)
DECLARE @.DomainUserName nchar(128)
-- First attempt to get the username stuffed into
-- context_info of this connection
SELECT @.ContextInfo = context_info
FROM master..sysprocesses
WHERE spid = @.@.SPID
-- Convert it to nvarchar
SET @.DomainUserName = CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
) AS
varbinary(128)) AS nvarchar(50))
-- If the context info is blank then we're on one of the connections
-- that Access dynamically created but the client code can't access
to stuff
-- something into the context_info. Interrogate all other spids for
this
-- user and this client process (using net_address to guarantee we've
got the
-- right user), to pull username from the context info of another
connection/spid
IF LEN(@.DomainUserName) < 1
BEGIN
DECLARE @.NetAddress nchar(12)
SELECT TOP 1 @.NetAddress = net_address
FROM master..sysprocesses p
WHERE SPID = @.@.SPID
SELECT TOP 1 @.ContextInfo = context_info
FROM master..sysprocesses p
INNER JOIN master..sysdatabases d
on p.dbid = d.dbid
WHERE hostprocess = HOST_ID()
AND d.Name = DB_NAME()
AND NOT CAST(context_info as nchar(50))= SPACE(50)
AND (LEN(@.NetAddress) < 1 OR net_address = @.NetAddress)
ORDER BY Context_info
SET @.DomainUserName =
CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
) AS varbinary(128)) AS
nvarchar(50))
END
/* Fail safe, if we couldn't find a non-empty Context_Info to
discover the
logged in user then could be the user is attached with Query
Analyzer or
some other method and we'll just use the user as they are logged
into SQL */
IF LEN(@.DomainUserName) < 1
SET @.DomainUserName = SYSTEM_USER
RETURN
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN
ame)-(CHARINDEX(N'',@.DomainUserName)))
END
GO
Solving the performance problem
Here are some workaround we've tried:
a.. Querying sysprocesses seems slow so we instead created a table
SyUserLogin and use HostID() and HostName() to find the right row. We've
seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2 users
have the same HostName. So there is no guarantee that when a user logs in
that HostID() and HostName() will uniquely identify each user. but the odds
are in our favor.
Basically here's what runs (stripped of the uniqueness checks) when the
user first logs in to SQL:
INSERT INTO [dbo].[SyUserLogin]
([UserName], [HostName], [HostID], [NetAddress])
SELECT @.SystemUserName, -- username that we pass in
Host_Name(),
Host_id(),
@.NetAddress -- from the sysprocesses row for this spid
Here's the new fnSystemUser:
CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
RETURN
(SELECT UpdateUserName
FROM SyUserLogin
WHERE HostName = Host_NAME()
AND HostID = HOST_ID()
)
END
a.. We tried to change the connection string to use either "Application
Name" or "Workstation ID" as a cubby hole to stuff the nt user name. We can
get this to work in a VB Script:
dim objConnection
dim strAccessConnect
strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer" & _
";Initial Catalog=MyDatabase" & _
";Persist Security Info=False;Application
Name=MyTestApp;Workstation ID=NTUserName;"
Set objConnection = createobject("ADODB.Connection")
objConnection.Open strAccessConnect, "OurProxy", "ProxyPassword"
Msgbox objConnection.ConnectionString
objconnection.close
set objconnection = nothing
But when we try this in Access2003, Access seems to stomp on those value
when it connects. We've played with the Connection dialog settings, but
can't seem to convince Access to leave our connection string values alone.
If we could get this to work then it would be ideal. Querying HostName() or
even Appname() although obscure would be as fast as SYSTEM_USER()
a.. Could we somehow create a function that would wrap context_info and
match the speed of SYSTEM_USER?
Thank you in advance for reading through this and commenting!
Kimberley Yochum kyochum@.wthq.comi dont want to sound dumb, but you've tried SUSER_SNAME() and all the other
ones, right'
also, if you're scanning against a large list of Text data, you might want
to consider using HASH INDEXES (aka the checksum function) in order to speed
this.
"Kimberley Yochum" <kyochum@.bigzoo.net> wrote in message
news:%23BOvPMX0EHA.632@.TK2MSFTNGP10.phx.gbl...
> We are moving an Access2000 adp application to Access 2003. Access2000
> version uses approle for security but we found Approle does not work the
> same in Access2003 so we are switching to a proxy security method.
> Problem
> We need a way for SQL to know the nt username that initiated the proxy
> connection. We need SQL to be able to retrieve that username very, very
> quickly (basically without a table lookup) for each user's spids,
regardless
> of how many connections Access decides to make for the adp.
> Details:
> a.. All the insert and update triggers depended on SYSTEM_USER to stamp
> the user who wrote the data on the row
> b.. Many stored procedures and views use a UDF that also depends on
> SYSTEM_USER
> c.. Since we're reconnecting each user as a proxy user, we need a way
for
> SQL to determine the nt user of the application.
> d.. Note: Modifying the client to pass the user in is simply not
practical
> due to the huge amount of code change.
> Our solution
> We are using SET CONTEXT_INFO to stuff the user's name into the
context_info
> of sysprocesses when the app first logs in. We have a UDF - fnSystemUser
> that queries sysprocesses to return what SYSTEM_USER used to.
Unfortunately,
> for the triggers or any UDFs (which previously only hit SYSTEM_USER), that
> meant a nose dive in performance since the UDF is executing on every row
> instead of evaluating once as if it were deterministic for the life of
that
> query. Although SYSTEM_USER is nondeterministic, it appears it was only
> evaluated once for the queries instead of the performance we see now on
our
> UDF returning the system user name from context_info, that indicates
> executing on every row.
> Since Access adps open multiple connections dynamically our fnSystemUser
is
> coded to find the Context_info of the spid that was first set through
client
> code. See the code below:
>
> CREATE FUNCTION dbo.fnSystemUser()
> RETURNS nvarchar(50)
> AS
> BEGIN
> DECLARE @.ContextInfo varbinary(128)
> DECLARE @.DomainUserName nchar(128)
> -- First attempt to get the username stuffed into
> -- context_info of this connection
> SELECT @.ContextInfo = context_info
> FROM master..sysprocesses
> WHERE spid = @.@.SPID
> -- Convert it to nvarchar
> SET @.DomainUserName = CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
) AS
> varbinary(128)) AS nvarchar(50))
> -- If the context info is blank then we're on one of the
connections
> -- that Access dynamically created but the client code can't access
> to stuff
> -- something into the context_info. Interrogate all other spids
for
> this
> -- user and this client process (using net_address to guarantee
we've
> got the
> -- right user), to pull username from the context info of another
> connection/spid
> IF LEN(@.DomainUserName) < 1
> BEGIN
> DECLARE @.NetAddress nchar(12)
> SELECT TOP 1 @.NetAddress = net_address
> FROM master..sysprocesses p
> WHERE SPID = @.@.SPID
> SELECT TOP 1 @.ContextInfo = context_info
> FROM master..sysprocesses p
> INNER JOIN master..sysdatabases d
> on p.dbid = d.dbid
> WHERE hostprocess = HOST_ID()
> AND d.Name = DB_NAME()
> AND NOT CAST(context_info as nchar(50))= SPACE(50)
> AND (LEN(@.NetAddress) < 1 OR net_address = @.NetAddress)
> ORDER BY Context_info
> SET @.DomainUserName =
> CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
) AS varbinary(128)) AS
> nvarchar(50))
> END
> /* Fail safe, if we couldn't find a non-empty Context_Info to
> discover the
> logged in user then could be the user is attached with Query
> Analyzer or
> some other method and we'll just use the user as they are logged
> into SQL */
> IF LEN(@.DomainUserName) < 1
> SET @.DomainUserName = SYSTEM_USER
> RETURN
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
> ame)-(CHARINDEX(N'',@.DomainUserName)))
> END
> GO
> Solving the performance problem
> Here are some workaround we've tried:
> a.. Querying sysprocesses seems slow so we instead created a table
> SyUserLogin and use HostID() and HostName() to find the right row. We've
> seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2 users
> have the same HostName. So there is no guarantee that when a user logs in
> that HostID() and HostName() will uniquely identify each user. but the[/vbcol]
odds
> are in our favor.
> Basically here's what runs (stripped of the uniqueness checks) when the
> user first logs in to SQL:
> INSERT INTO [dbo].[SyUserLogin]
> ([UserName], [HostName], [HostID], [NetAddress]
)
> SELECT @.SystemUserName, -- username that we pass in
> Host_Name(),
> Host_id(),
> @.NetAddress -- from the sysprocesses row for this spid
> Here's the new fnSystemUser:
> CREATE FUNCTION dbo.fnSystemUser()
> RETURNS nvarchar(50)
> AS
> BEGIN
> RETURN
> (SELECT UpdateUserName
> FROM SyUserLogin
> WHERE HostName = Host_NAME()
> AND HostID = HOST_ID()
> )
> END
>
> a.. We tried to change the connection string to use either "Application
> Name" or "Workstation ID" as a cubby hole to stuff the nt user name. We
can
> get this to work in a VB Script:
> dim objConnection
> dim strAccessConnect
> strAccessConnect = "Provider=SQLOLEDB.1" & _
> ";Net=dbnmpntw;Data Source=ASQLServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Persist Security Info=False;Application
> Name=MyTestApp;Workstation ID=NTUserName;"
> Set objConnection = createobject("ADODB.Connection")
> objConnection.Open strAccessConnect, "OurProxy", "ProxyPassword"
> Msgbox objConnection.ConnectionString
> objconnection.close
> set objconnection = nothing
>
> But when we try this in Access2003, Access seems to stomp on those value
> when it connects. We've played with the Connection dialog settings, but
> can't seem to convince Access to leave our connection string values alone.
> If we could get this to work then it would be ideal. Querying HostName()
or
> even Appname() although obscure would be as fast as SYSTEM_USER()
> a.. Could we somehow create a function that would wrap context_info and
> match the speed of SYSTEM_USER?
> Thank you in advance for reading through this and commenting!
> Kimberley Yochum kyochum@.wthq.com
>
>|||Thank you Aaron, but all the user functions (System_user, current_user,
session_user, user, user_name, suser_sname, etc.) return the Proxy user name
once a SQL user login has been performed.
Still hoping for some understanding about what Access is doing to my
connection string values or suggestions on what we can do in SQL...
Thank you in advance.
"aaron kempf" <aarkem@.safeco.com> wrote in message
news:OF9GDhZ0EHA.2228@.TK2MSFTNGP15.phx.gbl...
> i dont want to sound dumb, but you've tried SUSER_SNAME() and all the
other
> ones, right'
> also, if you're scanning against a large list of Text data, you might want
> to consider using HASH INDEXES (aka the checksum function) in order to
speed
> this.
>
>
> "Kimberley Yochum" <kyochum@.bigzoo.net> wrote in message
> news:%23BOvPMX0EHA.632@.TK2MSFTNGP10.phx.gbl...
> regardless
stamp[vbcol=seagreen]
> for
> practical
> context_info
> Unfortunately,
that[vbcol=seagreen]
> that
> our
> is
> client
AS[vbcol=seagreen]
> connections
access[vbcol=seagreen]
> for
> we've
logged[vbcol=seagreen]
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
users
in[vbcol=seagreen]
> odds
the[vbcol=seagreen]
"Application[vbcol=seagreen]
> can
alone.[vbcol=seagreen]
> or
and[vbcol=seagreen]
>|||can you use the application name value instead?
"Kimberley Yochum" <kyochum@.wthq.com> wrote in message
news:%23rCee%23Z0EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Thank you Aaron, but all the user functions (System_user, current_user,
> session_user, user, user_name, suser_sname, etc.) return the Proxy user
name
> once a SQL user login has been performed.
> Still hoping for some understanding about what Access is doing to my
> connection string values or suggestions on what we can do in SQL...
> Thank you in advance.
> "aaron kempf" <aarkem@.safeco.com> wrote in message
> news:OF9GDhZ0EHA.2228@.TK2MSFTNGP15.phx.gbl...
> other
want[vbcol=seagreen]
> speed
the[vbcol=seagreen]
very[vbcol=seagreen]
> stamp
way[vbcol=seagreen]
fnSystemUser[vbcol=seagreen]
> that
row[vbcol=seagreen]
only[vbcol=seagreen]
on[vbcol=seagreen]
fnSystemUser[vbcol=seagreen]
> AS
> access
spids[vbcol=seagreen]
another[vbcol=seagreen]
@.NetAddress)[vbcol=seagreen]
> logged
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
We've
> users
> in
> the
> "Application
We[vbcol=seagreen]
_[vbcol=seagreen]
value[vbcol=seagreen]
but[vbcol=seagreen]
> alone.
HostName()[vbcol=seagreen]
> and
>|||Yes I would love to but all my attempts to set an App name in the connection
string are being overwritten by Access.
Here's the connection string I tried. When I tried this in a VB script, it
worked fine and sysprocess showed the appname "MyTestApp" and the hostname
"NTUserName". But when I programmatically set the Access
CurrentProject.Connection by opening with the same connection string, those
value get tossed.
strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer"
& _
";Initial Catalog=MyDatabase" & _
";Persist Security
Info=False;Application Name=MyTestApp;Workstation ID=NTUserName;"
Any suggestions? Have you had any success in setting this before? I'd love
to hear about it!!
"david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
news:%23sSxhz30EHA.1740@.TK2MSFTNGP15.phx.gbl...
> can you use the application name value instead?
> "Kimberley Yochum" <kyochum@.wthq.com> wrote in message
> news:%23rCee%23Z0EHA.1404@.TK2MSFTNGP11.phx.gbl...
> name
> want
Access2000[vbcol=seagreen]
> the
proxy[vbcol=seagreen]
> very
on[vbcol=seagreen]
> way
> fnSystemUser
SYSTEM_USER),[vbcol=seagreen]
> row
of[vbcol=seagreen]
> only
> on
> fnSystemUser
through[vbcol=seagreen]
CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
)[vbcol=seagreen]
> spids
guarantee[vbcol=seagreen]
> another
> @.NetAddress)
Query[vbcol=seagreen]
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
> We've
logs
the[vbcol=seagreen]
when[vbcol=seagreen]
> We
&[vbcol=seagreen]
> _
Info=False;Application[vbcol=seagreen]
> value
> but
> HostName()
context_info[vbcol=seagreen]
>|||> string are being overwritten by Access.
rats. :~(
I'm an Access person, and I don't know anything else you could
try on the Access side. I guess you will be looking for a way
to cache the value, so that fnSystemUser doesn't need to do
SELECT query on every call, but I don't know what the options
are in SQL Server.
(david)
"Kimberley Yochum" <kyochum@.wthq.com> wrote in message
news:OhSjrRA1EHA.3448@.TK2MSFTNGP09.phx.gbl...
> Yes I would love to but all my attempts to set an App name in the
connection
> string are being overwritten by Access.
> Here's the connection string I tried. When I tried this in a VB script,
it
> worked fine and sysprocess showed the appname "MyTestApp" and the hostname
> "NTUserName". But when I programmatically set the Access
> CurrentProject.Connection by opening with the same connection string,
those
> value get tossed.
> strAccessConnect = "Provider=SQLOLEDB.1" & _
> ";Net=dbnmpntw;Data
Source=ASQLServer"
> & _
> ";Initial Catalog=MyDatabase" & _
> ";Persist Security
> Info=False;Application Name=MyTestApp;Workstation ID=NTUserName;"
>
> Any suggestions? Have you had any success in setting this before? I'd
love
> to hear about it!!
> "david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
> news:%23sSxhz30EHA.1740@.TK2MSFTNGP15.phx.gbl...
current_user,[vbcol=seagreen]
user[vbcol=seagreen]
the[vbcol=seagreen]
might[vbcol=seagreen]
to[vbcol=seagreen]
> Access2000
work[vbcol=seagreen]
> proxy
to[vbcol=seagreen]
> on
a[vbcol=seagreen]
> SYSTEM_USER),
every[vbcol=seagreen]
life[vbcol=seagreen]
> of
now[vbcol=seagreen]
indicates[vbcol=seagreen]
> through
> CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
)
can't[vbcol=seagreen]
> guarantee
SPACE(50)[vbcol=seagreen]
to[vbcol=seagreen]
> Query
are[vbcol=seagreen]
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
table
2[vbcol=seagreen]
> logs
> the
> when
name.[vbcol=seagreen]
Source=ASQLServer"[vbcol=seagreen]
> &
> Info=False;Application
settings,[vbcol=seagreen]
values[vbcol=seagreen]
> context_info
>|||try with current_user() or user_name()
"david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
news:OHiFPga1EHA.2568@.TK2MSFTNGP11.phx.gbl...
> rats. :~(
> I'm an Access person, and I don't know anything else you could
> try on the Access side. I guess you will be looking for a way
> to cache the value, so that fnSystemUser doesn't need to do
> SELECT query on every call, but I don't know what the options
> are in SQL Server.
> (david)
>
> "Kimberley Yochum" <kyochum@.wthq.com> wrote in message
> news:OhSjrRA1EHA.3448@.TK2MSFTNGP09.phx.gbl...
> connection
> it
hostname[vbcol=seagreen]
> those
> Source=ASQLServer"
> love
> current_user,
> user
> the
> might
order[vbcol=seagreen]
> to
> work
method.[vbcol=seagreen]
very,[vbcol=seagreen]
spids,[vbcol=seagreen]
> to
depends[vbcol=seagreen]
need[vbcol=seagreen]
> a
not[vbcol=seagreen]
> every
> life
was[vbcol=seagreen]
> now
> indicates
> can't
other[vbcol=seagreen]
> SPACE(50)
Context_Info[vbcol=seagreen]
> to
> are
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
> table
row.
seen[vbcol=seagreen]
> 2
user[vbcol=seagreen]
but[vbcol=seagreen]
spid[vbcol=seagreen]
> name.
> Source=ASQLServer"
those[vbcol=seagreen]
> settings,
> values
SYSTEM_USER()[vbcol=seagreen]
>sql
version uses approle for security but we found Approle does not work the
same in Access2003 so we are switching to a proxy security method.
Problem
We need a way for SQL to know the nt username that initiated the proxy
connection. We need SQL to be able to retrieve that username very, very
quickly (basically without a table lookup) for each user's spids, regardless
of how many connections Access decides to make for the adp.
Details:
a.. All the insert and update triggers depended on SYSTEM_USER to stamp
the user who wrote the data on the row
b.. Many stored procedures and views use a UDF that also depends on
SYSTEM_USER
c.. Since we're reconnecting each user as a proxy user, we need a way for
SQL to determine the nt user of the application.
d.. Note: Modifying the client to pass the user in is simply not practical
due to the huge amount of code change.
Our solution
We are using SET CONTEXT_INFO to stuff the user's name into the context_info
of sysprocesses when the app first logs in. We have a UDF - fnSystemUser
that queries sysprocesses to return what SYSTEM_USER used to. Unfortunately,
for the triggers or any UDFs (which previously only hit SYSTEM_USER), that
meant a nose dive in performance since the UDF is executing on every row
instead of evaluating once as if it were deterministic for the life of that
query. Although SYSTEM_USER is nondeterministic, it appears it was only
evaluated once for the queries instead of the performance we see now on our
UDF returning the system user name from context_info, that indicates
executing on every row.
Since Access adps open multiple connections dynamically our fnSystemUser is
coded to find the Context_info of the spid that was first set through client
code. See the code below:
CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @.ContextInfo varbinary(128)
DECLARE @.DomainUserName nchar(128)
-- First attempt to get the username stuffed into
-- context_info of this connection
SELECT @.ContextInfo = context_info
FROM master..sysprocesses
WHERE spid = @.@.SPID
-- Convert it to nvarchar
SET @.DomainUserName = CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
) AS
varbinary(128)) AS nvarchar(50))
-- If the context info is blank then we're on one of the connections
-- that Access dynamically created but the client code can't access
to stuff
-- something into the context_info. Interrogate all other spids for
this
-- user and this client process (using net_address to guarantee we've
got the
-- right user), to pull username from the context info of another
connection/spid
IF LEN(@.DomainUserName) < 1
BEGIN
DECLARE @.NetAddress nchar(12)
SELECT TOP 1 @.NetAddress = net_address
FROM master..sysprocesses p
WHERE SPID = @.@.SPID
SELECT TOP 1 @.ContextInfo = context_info
FROM master..sysprocesses p
INNER JOIN master..sysdatabases d
on p.dbid = d.dbid
WHERE hostprocess = HOST_ID()
AND d.Name = DB_NAME()
AND NOT CAST(context_info as nchar(50))= SPACE(50)
AND (LEN(@.NetAddress) < 1 OR net_address = @.NetAddress)
ORDER BY Context_info
SET @.DomainUserName =
CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
) AS varbinary(128)) AS
nvarchar(50))
END
/* Fail safe, if we couldn't find a non-empty Context_Info to
discover the
logged in user then could be the user is attached with Query
Analyzer or
some other method and we'll just use the user as they are logged
into SQL */
IF LEN(@.DomainUserName) < 1
SET @.DomainUserName = SYSTEM_USER
RETURN
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN
ame)-(CHARINDEX(N'',@.DomainUserName)))
END
GO
Solving the performance problem
Here are some workaround we've tried:
a.. Querying sysprocesses seems slow so we instead created a table
SyUserLogin and use HostID() and HostName() to find the right row. We've
seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2 users
have the same HostName. So there is no guarantee that when a user logs in
that HostID() and HostName() will uniquely identify each user. but the odds
are in our favor.
Basically here's what runs (stripped of the uniqueness checks) when the
user first logs in to SQL:
INSERT INTO [dbo].[SyUserLogin]
([UserName], [HostName], [HostID], [NetAddress])
SELECT @.SystemUserName, -- username that we pass in
Host_Name(),
Host_id(),
@.NetAddress -- from the sysprocesses row for this spid
Here's the new fnSystemUser:
CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
RETURN
(SELECT UpdateUserName
FROM SyUserLogin
WHERE HostName = Host_NAME()
AND HostID = HOST_ID()
)
END
a.. We tried to change the connection string to use either "Application
Name" or "Workstation ID" as a cubby hole to stuff the nt user name. We can
get this to work in a VB Script:
dim objConnection
dim strAccessConnect
strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer" & _
";Initial Catalog=MyDatabase" & _
";Persist Security Info=False;Application
Name=MyTestApp;Workstation ID=NTUserName;"
Set objConnection = createobject("ADODB.Connection")
objConnection.Open strAccessConnect, "OurProxy", "ProxyPassword"
Msgbox objConnection.ConnectionString
objconnection.close
set objconnection = nothing
But when we try this in Access2003, Access seems to stomp on those value
when it connects. We've played with the Connection dialog settings, but
can't seem to convince Access to leave our connection string values alone.
If we could get this to work then it would be ideal. Querying HostName() or
even Appname() although obscure would be as fast as SYSTEM_USER()
a.. Could we somehow create a function that would wrap context_info and
match the speed of SYSTEM_USER?
Thank you in advance for reading through this and commenting!
Kimberley Yochum kyochum@.wthq.comi dont want to sound dumb, but you've tried SUSER_SNAME() and all the other
ones, right'
also, if you're scanning against a large list of Text data, you might want
to consider using HASH INDEXES (aka the checksum function) in order to speed
this.
"Kimberley Yochum" <kyochum@.bigzoo.net> wrote in message
news:%23BOvPMX0EHA.632@.TK2MSFTNGP10.phx.gbl...
> We are moving an Access2000 adp application to Access 2003. Access2000
> version uses approle for security but we found Approle does not work the
> same in Access2003 so we are switching to a proxy security method.
> Problem
> We need a way for SQL to know the nt username that initiated the proxy
> connection. We need SQL to be able to retrieve that username very, very
> quickly (basically without a table lookup) for each user's spids,
regardless
> of how many connections Access decides to make for the adp.
> Details:
> a.. All the insert and update triggers depended on SYSTEM_USER to stamp
> the user who wrote the data on the row
> b.. Many stored procedures and views use a UDF that also depends on
> SYSTEM_USER
> c.. Since we're reconnecting each user as a proxy user, we need a way
for
> SQL to determine the nt user of the application.
> d.. Note: Modifying the client to pass the user in is simply not
practical
> due to the huge amount of code change.
> Our solution
> We are using SET CONTEXT_INFO to stuff the user's name into the
context_info
> of sysprocesses when the app first logs in. We have a UDF - fnSystemUser
> that queries sysprocesses to return what SYSTEM_USER used to.
Unfortunately,
> for the triggers or any UDFs (which previously only hit SYSTEM_USER), that
> meant a nose dive in performance since the UDF is executing on every row
> instead of evaluating once as if it were deterministic for the life of
that
> query. Although SYSTEM_USER is nondeterministic, it appears it was only
> evaluated once for the queries instead of the performance we see now on
our
> UDF returning the system user name from context_info, that indicates
> executing on every row.
> Since Access adps open multiple connections dynamically our fnSystemUser
is
> coded to find the Context_info of the spid that was first set through
client
> code. See the code below:
>
> CREATE FUNCTION dbo.fnSystemUser()
> RETURNS nvarchar(50)
> AS
> BEGIN
> DECLARE @.ContextInfo varbinary(128)
> DECLARE @.DomainUserName nchar(128)
> -- First attempt to get the username stuffed into
> -- context_info of this connection
> SELECT @.ContextInfo = context_info
> FROM master..sysprocesses
> WHERE spid = @.@.SPID
> -- Convert it to nvarchar
> SET @.DomainUserName = CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
) AS
> varbinary(128)) AS nvarchar(50))
> -- If the context info is blank then we're on one of the
connections
> -- that Access dynamically created but the client code can't access
> to stuff
> -- something into the context_info. Interrogate all other spids
for
> this
> -- user and this client process (using net_address to guarantee
we've
> got the
> -- right user), to pull username from the context info of another
> connection/spid
> IF LEN(@.DomainUserName) < 1
> BEGIN
> DECLARE @.NetAddress nchar(12)
> SELECT TOP 1 @.NetAddress = net_address
> FROM master..sysprocesses p
> WHERE SPID = @.@.SPID
> SELECT TOP 1 @.ContextInfo = context_info
> FROM master..sysprocesses p
> INNER JOIN master..sysdatabases d
> on p.dbid = d.dbid
> WHERE hostprocess = HOST_ID()
> AND d.Name = DB_NAME()
> AND NOT CAST(context_info as nchar(50))= SPACE(50)
> AND (LEN(@.NetAddress) < 1 OR net_address = @.NetAddress)
> ORDER BY Context_info
> SET @.DomainUserName =
> CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
) AS varbinary(128)) AS
> nvarchar(50))
> END
> /* Fail safe, if we couldn't find a non-empty Context_Info to
> discover the
> logged in user then could be the user is attached with Query
> Analyzer or
> some other method and we'll just use the user as they are logged
> into SQL */
> IF LEN(@.DomainUserName) < 1
> SET @.DomainUserName = SYSTEM_USER
> RETURN
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
> ame)-(CHARINDEX(N'',@.DomainUserName)))
> END
> GO
> Solving the performance problem
> Here are some workaround we've tried:
> a.. Querying sysprocesses seems slow so we instead created a table
> SyUserLogin and use HostID() and HostName() to find the right row. We've
> seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2 users
> have the same HostName. So there is no guarantee that when a user logs in
> that HostID() and HostName() will uniquely identify each user. but the[/vbcol]
odds
> are in our favor.
> Basically here's what runs (stripped of the uniqueness checks) when the
> user first logs in to SQL:
> INSERT INTO [dbo].[SyUserLogin]
> ([UserName], [HostName], [HostID], [NetAddress]
)
> SELECT @.SystemUserName, -- username that we pass in
> Host_Name(),
> Host_id(),
> @.NetAddress -- from the sysprocesses row for this spid
> Here's the new fnSystemUser:
> CREATE FUNCTION dbo.fnSystemUser()
> RETURNS nvarchar(50)
> AS
> BEGIN
> RETURN
> (SELECT UpdateUserName
> FROM SyUserLogin
> WHERE HostName = Host_NAME()
> AND HostID = HOST_ID()
> )
> END
>
> a.. We tried to change the connection string to use either "Application
> Name" or "Workstation ID" as a cubby hole to stuff the nt user name. We
can
> get this to work in a VB Script:
> dim objConnection
> dim strAccessConnect
> strAccessConnect = "Provider=SQLOLEDB.1" & _
> ";Net=dbnmpntw;Data Source=ASQLServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Persist Security Info=False;Application
> Name=MyTestApp;Workstation ID=NTUserName;"
> Set objConnection = createobject("ADODB.Connection")
> objConnection.Open strAccessConnect, "OurProxy", "ProxyPassword"
> Msgbox objConnection.ConnectionString
> objconnection.close
> set objconnection = nothing
>
> But when we try this in Access2003, Access seems to stomp on those value
> when it connects. We've played with the Connection dialog settings, but
> can't seem to convince Access to leave our connection string values alone.
> If we could get this to work then it would be ideal. Querying HostName()
or
> even Appname() although obscure would be as fast as SYSTEM_USER()
> a.. Could we somehow create a function that would wrap context_info and
> match the speed of SYSTEM_USER?
> Thank you in advance for reading through this and commenting!
> Kimberley Yochum kyochum@.wthq.com
>
>|||Thank you Aaron, but all the user functions (System_user, current_user,
session_user, user, user_name, suser_sname, etc.) return the Proxy user name
once a SQL user login has been performed.
Still hoping for some understanding about what Access is doing to my
connection string values or suggestions on what we can do in SQL...
Thank you in advance.
"aaron kempf" <aarkem@.safeco.com> wrote in message
news:OF9GDhZ0EHA.2228@.TK2MSFTNGP15.phx.gbl...
> i dont want to sound dumb, but you've tried SUSER_SNAME() and all the
other
> ones, right'
> also, if you're scanning against a large list of Text data, you might want
> to consider using HASH INDEXES (aka the checksum function) in order to
speed
> this.
>
>
> "Kimberley Yochum" <kyochum@.bigzoo.net> wrote in message
> news:%23BOvPMX0EHA.632@.TK2MSFTNGP10.phx.gbl...
> regardless
stamp[vbcol=seagreen]
> for
> practical
> context_info
> Unfortunately,
that[vbcol=seagreen]
> that
> our
> is
> client
AS[vbcol=seagreen]
> connections
access[vbcol=seagreen]
> for
> we've
logged[vbcol=seagreen]
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
users
in[vbcol=seagreen]
> odds
the[vbcol=seagreen]
"Application[vbcol=seagreen]
> can
alone.[vbcol=seagreen]
> or
and[vbcol=seagreen]
>|||can you use the application name value instead?
"Kimberley Yochum" <kyochum@.wthq.com> wrote in message
news:%23rCee%23Z0EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Thank you Aaron, but all the user functions (System_user, current_user,
> session_user, user, user_name, suser_sname, etc.) return the Proxy user
name
> once a SQL user login has been performed.
> Still hoping for some understanding about what Access is doing to my
> connection string values or suggestions on what we can do in SQL...
> Thank you in advance.
> "aaron kempf" <aarkem@.safeco.com> wrote in message
> news:OF9GDhZ0EHA.2228@.TK2MSFTNGP15.phx.gbl...
> other
want[vbcol=seagreen]
> speed
the[vbcol=seagreen]
very[vbcol=seagreen]
> stamp
way[vbcol=seagreen]
fnSystemUser[vbcol=seagreen]
> that
row[vbcol=seagreen]
only[vbcol=seagreen]
on[vbcol=seagreen]
fnSystemUser[vbcol=seagreen]
> AS
> access
spids[vbcol=seagreen]
another[vbcol=seagreen]
@.NetAddress)[vbcol=seagreen]
> logged
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
We've
> users
> in
> the
> "Application
We[vbcol=seagreen]
_[vbcol=seagreen]
value[vbcol=seagreen]
but[vbcol=seagreen]
> alone.
HostName()[vbcol=seagreen]
> and
>|||Yes I would love to but all my attempts to set an App name in the connection
string are being overwritten by Access.
Here's the connection string I tried. When I tried this in a VB script, it
worked fine and sysprocess showed the appname "MyTestApp" and the hostname
"NTUserName". But when I programmatically set the Access
CurrentProject.Connection by opening with the same connection string, those
value get tossed.
strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer"
& _
";Initial Catalog=MyDatabase" & _
";Persist Security
Info=False;Application Name=MyTestApp;Workstation ID=NTUserName;"
Any suggestions? Have you had any success in setting this before? I'd love
to hear about it!!
"david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
news:%23sSxhz30EHA.1740@.TK2MSFTNGP15.phx.gbl...
> can you use the application name value instead?
> "Kimberley Yochum" <kyochum@.wthq.com> wrote in message
> news:%23rCee%23Z0EHA.1404@.TK2MSFTNGP11.phx.gbl...
> name
> want
Access2000[vbcol=seagreen]
> the
proxy[vbcol=seagreen]
> very
on[vbcol=seagreen]
> way
> fnSystemUser
SYSTEM_USER),[vbcol=seagreen]
> row
of[vbcol=seagreen]
> only
> on
> fnSystemUser
through[vbcol=seagreen]
CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
)[vbcol=seagreen]
> spids
guarantee[vbcol=seagreen]
> another
> @.NetAddress)
Query[vbcol=seagreen]
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
> We've
logs
the[vbcol=seagreen]
when[vbcol=seagreen]
> We
&[vbcol=seagreen]
> _
Info=False;Application[vbcol=seagreen]
> value
> but
> HostName()
context_info[vbcol=seagreen]
>|||> string are being overwritten by Access.
rats. :~(
I'm an Access person, and I don't know anything else you could
try on the Access side. I guess you will be looking for a way
to cache the value, so that fnSystemUser doesn't need to do
SELECT query on every call, but I don't know what the options
are in SQL Server.
(david)
"Kimberley Yochum" <kyochum@.wthq.com> wrote in message
news:OhSjrRA1EHA.3448@.TK2MSFTNGP09.phx.gbl...
> Yes I would love to but all my attempts to set an App name in the
connection
> string are being overwritten by Access.
> Here's the connection string I tried. When I tried this in a VB script,
it
> worked fine and sysprocess showed the appname "MyTestApp" and the hostname
> "NTUserName". But when I programmatically set the Access
> CurrentProject.Connection by opening with the same connection string,
those
> value get tossed.
> strAccessConnect = "Provider=SQLOLEDB.1" & _
> ";Net=dbnmpntw;Data
Source=ASQLServer"
> & _
> ";Initial Catalog=MyDatabase" & _
> ";Persist Security
> Info=False;Application Name=MyTestApp;Workstation ID=NTUserName;"
>
> Any suggestions? Have you had any success in setting this before? I'd
love
> to hear about it!!
> "david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
> news:%23sSxhz30EHA.1740@.TK2MSFTNGP15.phx.gbl...
current_user,[vbcol=seagreen]
user[vbcol=seagreen]
the[vbcol=seagreen]
might[vbcol=seagreen]
to[vbcol=seagreen]
> Access2000
work[vbcol=seagreen]
> proxy
to[vbcol=seagreen]
> on
a[vbcol=seagreen]
> SYSTEM_USER),
every[vbcol=seagreen]
life[vbcol=seagreen]
> of
now[vbcol=seagreen]
indicates[vbcol=seagreen]
> through
> CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
)
can't[vbcol=seagreen]
> guarantee
SPACE(50)[vbcol=seagreen]
to[vbcol=seagreen]
> Query
are[vbcol=seagreen]
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
table
2[vbcol=seagreen]
> logs
> the
> when
name.[vbcol=seagreen]
Source=ASQLServer"[vbcol=seagreen]
> &
> Info=False;Application
settings,[vbcol=seagreen]
values[vbcol=seagreen]
> context_info
>|||try with current_user() or user_name()
"david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
news:OHiFPga1EHA.2568@.TK2MSFTNGP11.phx.gbl...
> rats. :~(
> I'm an Access person, and I don't know anything else you could
> try on the Access side. I guess you will be looking for a way
> to cache the value, so that fnSystemUser doesn't need to do
> SELECT query on every call, but I don't know what the options
> are in SQL Server.
> (david)
>
> "Kimberley Yochum" <kyochum@.wthq.com> wrote in message
> news:OhSjrRA1EHA.3448@.TK2MSFTNGP09.phx.gbl...
> connection
> it
hostname[vbcol=seagreen]
> those
> Source=ASQLServer"
> love
> current_user,
> user
> the
> might
order[vbcol=seagreen]
> to
> work
method.[vbcol=seagreen]
very,[vbcol=seagreen]
spids,[vbcol=seagreen]
> to
depends[vbcol=seagreen]
need[vbcol=seagreen]
> a
not[vbcol=seagreen]
> every
> life
was[vbcol=seagreen]
> now
> indicates
> can't
other[vbcol=seagreen]
> SPACE(50)
Context_Info[vbcol=seagreen]
> to
> are
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
> table
row.
seen[vbcol=seagreen]
> 2
user[vbcol=seagreen]
but[vbcol=seagreen]
spid[vbcol=seagreen]
> name.
> Source=ASQLServer"
those[vbcol=seagreen]
> settings,
> values
SYSTEM_USER()[vbcol=seagreen]
>sql
Access 2000 to SQL Express 2005
I have a commercial applicaton today developed in visual basic 6 with an access 2000 database. This application is ran by hundreds of businesses nationwide. Each business is running anywhere from 1-8 computer systems.
Every once in a while a customer experiences corruption with the Access 2000 DB. Because of this I would like to switch the backend to SQL Server express 2005.
1) Does SQL Server Express need to be installed on every single PC even if the user of a PC are going to access the database on another machine? ie PCa and PCb will connect to PCf for data. Does PCa and PCb have to have sql express installed?
2)Can I install SQL Express and the .net framework 2.0 silently with my application and can i configure network access automatically. What tool is best for this WISE? Installshield, etc? I need to be able to deploy 1 simple exe to install the entire application.
3)What type of a file size are we looking at here for deployment? Express + .net framework 2.0 60-80 Meg?
3)Can I backup the database with VB6? Articles say no but can't you just xcopy the MDF and log file to backup the database?
4)Can I update the database from VB6 utilizing MSADOX. With Access 2000 I can add tables, modify columns, indexes etc using MSADOX right from VB.
Hi Matt,
1) No, you only need SQL Express installed on the computer where the data will be stored.
2) Yes, you can install the framework and SQL Express silently. I'm not sure of the switch for the framework, but it's probably /q. For SQL Express, check out the Books Online topic on command line installation at http://msdn2.microsoft.com/ms144259.aspx. Network access is configured with the DISABLENETWORKPROTOCOLS switch. You will also need to make Exceptions in any firewalls on the computer where you install SQL Express and the SQL setup does not do that for you. You should be able to find the Windows Firewall API on MSDN. Finally, SQL Express must be deployed using the pre-build installer that you download. You can embed that installer within any other installation program using a command line as described in the BOL topic listed above.
3) The .NET Framework 2.0 redistributable is 22.4 MG and SQL Express is 53.5 MB.
4) XCopy of the MDF file does not account for transactions that are in process at the time of the copy, so your backup could be in an odd state if you try to use copy. I'm not really the expert on SQL Express integration with VB6, but you can do backups of SQL database using a fairly simple T-SQL script and then use the Windows Task Scheduler to schedule the script to run so that backups are made on a regular basis.
5) I don't see any reason why not, but again, I'm not the expert here. Needless to say, SQL 2005 is tightly integrated with Visual Studio 2005 and ADO.NET 2.0 so that would be the best way to do this, but obviously your app is in VB6. My best advice is to give it a test. This might also be a good oportunity to consider migrating your application to VB.NET 2005. You can check out VS 2005 for free using the Express edtions at http://msdn.microsoft.com/vstudio/express/.
Regards,
Mike Wachal
SQL Express
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 Sql Server Over Internet
I need to develop an application in vb.net that uses sql server database so that user can access the use the application over the inter net.
How Can I accomplish that ?
What are the options ?
Pls Help..http://www.fawcette.com/vsm/2003_12/magazine/columns/gettingstarted/
How Can I accomplish that ?
What are the options ?
Pls Help..http://www.fawcette.com/vsm/2003_12/magazine/columns/gettingstarted/
Sunday, March 11, 2012
Accelerating MS SQL Client
Does a product exist that might solve the following problem?
I have an application developed in VB6 that accesses data residing on a
central Microsoft SQL server in our datacenter. While I would argue
that this application stinks, I have been assigned to deploy it to the
laptops of about 100 people. These people travel to sites and connect
to the SQL server using a VPN connection and MDAC 2.8. In testing, I
found that the performance of this application suffers when connecting
to SQL via VPN, and since this application seems to hammer SQL when
loading menus in the least optimum way possible, the program will hang
while waiting for data.
Is there software I can install on the users' laptops that might
"accelerate" the connection to the SQL server? Perhaps this software
might be an alternative to the Microsoft-provided SQL client? I saw a
product called ZCache that seems to provide some caching functionality
for Oracle and MySQL databases, but nothing for MS SQL.Joe (josephtermine@.hotmail.com) writes:
> Does a product exist that might solve the following problem?
> I have an application developed in VB6 that accesses data residing on a
> central Microsoft SQL server in our datacenter. While I would argue
> that this application stinks, I have been assigned to deploy it to the
> laptops of about 100 people. These people travel to sites and connect
> to the SQL server using a VPN connection and MDAC 2.8. In testing, I
> found that the performance of this application suffers when connecting
> to SQL via VPN, and since this application seems to hammer SQL when
> loading menus in the least optimum way possible, the program will hang
> while waiting for data.
> Is there software I can install on the users' laptops that might
> "accelerate" the connection to the SQL server? Perhaps this software
> might be an alternative to the Microsoft-provided SQL client? I saw a
> product called ZCache that seems to provide some caching functionality
> for Oracle and MySQL databases, but nothing for MS SQL.
That sounds like about an impossible thing for me. Then again, if there is
such a product for Oracle or MySQL, I guess it should be possible for
MS SQL Server. Yet then again, the accellerating effect of that product
may apply only to certain usage patterns.
Of course, if one analyses what this product is upto, it is possible
that you could implement a local cache, but it would certainly be
far cheaper to fix the application itself. It sounds if there are
tons of roundtrips and server-side cursors in it.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Joe" <josephtermine@.hotmail.com> wrote in message
news:1108413195.029686.70090@.z14g2000cwz.googlegro ups.com...
> Does a product exist that might solve the following problem?
> I have an application developed in VB6 that accesses data residing on a
> central Microsoft SQL server in our datacenter. While I would argue
> that this application stinks, I have been assigned to deploy it to the
> laptops of about 100 people. These people travel to sites and connect
> to the SQL server using a VPN connection and MDAC 2.8. In testing, I
> found that the performance of this application suffers when connecting
> to SQL via VPN, and since this application seems to hammer SQL when
> loading menus in the least optimum way possible, the program will hang
> while waiting for data.
> Is there software I can install on the users' laptops that might
> "accelerate" the connection to the SQL server? Perhaps this software
> might be an alternative to the Microsoft-provided SQL client? I saw a
> product called ZCache that seems to provide some caching functionality
> for Oracle and MySQL databases, but nothing for MS SQL.
If you just connect VB6 bound datagrids to sql server and that's your lot
then you'll potentially get a hell of a lot of IO.
Plus the ones in the box didn't work so well, maybe they're patched or third
party or whatever.
It does sound that the app was developed without considering what's likely
over something like a 28k connection.
I've used vpn.
It can be very slow.
It can also have a limit on concurrent users, depending on how you're
vpn-ing.
Personally, I would have thought caching some data in a local database of
some sort a must.
At least for the sort of app this sounds like.
Whether that would best be an access mdb, full msde installation or whatever
would depend on the details.
vb.net and xml on the laptops would be another alternative but quite likely
involving a complete rewrite.
--
Regards,
Andy O'Neill|||I suspect that this application does use the VB6 grids out of the box,
and these are known to be resource-intensive. The newer VB.NET grids
seem to perform better in disconnected environments.
Anyway, there doesn't seem to be much we can do if we don't have access
to the VB6 source code.
Might there be a VPN accelerator we could try? Would this help us much?|||There are tons of round-trips with this application, but as we don't
have access to the VB6 source (this is an off-the-shelf product) there
might not be much we can do to fix the inefficiencies or implement a
local cache. There's a lot to think about.|||Joe (josephtermine@.hotmail.com) writes:
> There are tons of round-trips with this application, but as we don't
> have access to the VB6 source (this is an off-the-shelf product) there
> might not be much we can do to fix the inefficiencies or implement a
> local cache. There's a lot to think about.
The one thing I can think would be replication. Assuming that you can
update from the app, that would be merge replication.
And, no, I have experience of merge replication. (And not much of other
sorts of replication either.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 15 Feb 2005 10:33:18 -0800, Joe wrote:
> I suspect that this application does use the VB6 grids out of the box,
> and these are known to be resource-intensive. The newer VB.NET grids
> seem to perform better in disconnected environments.
> Anyway, there doesn't seem to be much we can do if we don't have access
> to the VB6 source code.
> Might there be a VPN accelerator we could try? Would this help us much?
Perhaps this could help?
http://sqlrelay.sourceforge.net/
It looks complicated, but it might suit you.
Replication of the database to a local MSDE might be another choice.|||"Joe" <josephtermine@.hotmail.com> wrote in message
news:1108492398.045929.319930@.l41g2000cwc.googlegr oups.com...
>I suspect that this application does use the VB6 grids out of the box,
> and these are known to be resource-intensive. The newer VB.NET grids
> seem to perform better in disconnected environments.
I used to use flexgrid to display data, user clicks on a row and gets a
bunch of text/combo boxes to work on it .
I worte my own code to update.
This was because of the bugs in the datagrid.
You might want to investigate those.
I forget but there are circs when updates just didn't happen.
WIth dotnet I use the datagrids and they're good.
The dataadaptor and (disconnected) dataset functionality is very useful.
There would be issues meant added code in an app where a salesman wants to
use the thing completely disconnected from the database.
> Anyway, there doesn't seem to be much we can do if we don't have access
> to the VB6 source code.
Well... that's a mistake someone's made right there.
> Might there be a VPN accelerator we could try? Would this help us much?
No idea mate.
--
Regards,
Andy O'Neill
Subscribe to:
Posts (Atom)