Are all DSN's really ODBC?

I'm still trying to learn the alphabet soup of MS's data access methods, but
one thing I just noticed is that the Data Sources control panel is called
"Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
my code? I am under the impression that ODBC is considerably slower than
OLEDB or other technologies (although what the differences are I don't really
know).
For instance, I have my code literally sprinkled with little ADO lookups
that use a single connection string...
"ODBC;DATABASE=OurDB;DSN=SQL Server"
Removing the "ODBC" does nothing, which I assume is because it's not a
key/value pair. But it still uses a DSN to look up the connection, so does
this mean every query I run using this connection string uses ODBC?
If so, should I really expect any sort of real-world performance boost if I
use a new string, one like...
"Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
Is there an even better provider to use?
It's all so confusing!!
Maury
Why use DSN's at all? Go DSN-less. You just need to tack a few things onto
the connection string.
Head to www.connectionstrings.com for more info.
Keith
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods,
> but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's
> in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't
> really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
> I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury
|||"Keith Kratochvil" wrote:

> Why use DSN's at all?
That's what I'm asking.
Maury
|||yes.
Choose an OLEDB Provider and do not use a DSN.
OLEDB is going to be faster particularly when you choose the native provider
for your target platform (Example..."SQL Server")
Greg Jackson
PDX, Oregon
|||In addition tot he other comments:
ODBC is a programming API. A low-level API. There exists higher level abstractions such as the RDO
object model on top of ODBC. ODBC uses a driver to connect to the DBMS.
OLEDB is a programming API. A low-level API. There exists higher level abstractions such as the ADO
object model on top of OLEDB. OLEDB uses a driver to connect to the DBMS. There exists an OLEDB
driver which sits on top of ODBC. This is probably what you are seeing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods, but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury

Are all DSN's really ODBC?

I'm still trying to learn the alphabet soup of MS's data access methods, but
one thing I just noticed is that the Data Sources control panel is called
"Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
my code? I am under the impression that ODBC is considerably slower than
OLEDB or other technologies (although what the differences are I don't reall
y
know).
For instance, I have my code literally sprinkled with little ADO lookups
that use a single connection string...
"ODBC;DATABASE=OurDB;DSN=SQL Server"
Removing the "ODBC" does nothing, which I assume is because it's not a
key/value pair. But it still uses a DSN to look up the connection, so does
this mean every query I run using this connection string uses ODBC?
If so, should I really expect any sort of real-world performance boost if I
use a new string, one like...
" Provider=sqloledb;DATABASE=OurDB;DSN=SQL
Server"
Is there an even better provider to use?
It's all so confusing!!
MauryWhy use DSN's at all? Go DSN-less. You just need to tack a few things onto
the connection string.
Head to www.connectionstrings.com for more info.
Keith
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods,
> but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's
> in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't
> really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
> I
> use a new string, one like...
> " Provider=sqloledb;DATABASE=OurDB;DSN=SQL
Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury|||"Keith Kratochvil" wrote:

> Why use DSN's at all?
That's what I'm asking.
Maury|||yes.
Choose an OLEDB Provider and do not use a DSN.
OLEDB is going to be faster particularly when you choose the native provider
for your target platform (Example..."SQL Server")
Greg Jackson
PDX, Oregon|||In addition tot he other comments:
ODBC is a programming API. A low-level API. There exists higher level abstra
ctions such as the RDO
object model on top of ODBC. ODBC uses a driver to connect to the DBMS.
OLEDB is a programming API. A low-level API. There exists higher level abstr
actions such as the ADO
object model on top of OLEDB. OLEDB uses a driver to connect to the DBMS. Th
ere exists an OLEDB
driver which sits on top of ODBC. This is probably what you are seeing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in messag
e
news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods, b
ut
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's i
n
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't rea
lly
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
I
> use a new string, one like...
> " Provider=sqloledb;DATABASE=OurDB;DSN=SQL
Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maurysql

Are all DSN's really ODBC?

I'm still trying to learn the alphabet soup of MS's data access methods, but
one thing I just noticed is that the Data Sources control panel is called
"Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
my code? I am under the impression that ODBC is considerably slower than
OLEDB or other technologies (although what the differences are I don't really
know).
For instance, I have my code literally sprinkled with little ADO lookups
that use a single connection string...
"ODBC;DATABASE=OurDB;DSN=SQL Server"
Removing the "ODBC" does nothing, which I assume is because it's not a
key/value pair. But it still uses a DSN to look up the connection, so does
this mean every query I run using this connection string uses ODBC?
If so, should I really expect any sort of real-world performance boost if I
use a new string, one like...
"Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
Is there an even better provider to use?
It's all so confusing!!
MauryWhy use DSN's at all? Go DSN-less. You just need to tack a few things onto
the connection string.
Head to www.connectionstrings.com for more info.
--
Keith
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods,
> but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's
> in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't
> really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
> I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury|||"Keith Kratochvil" wrote:
> Why use DSN's at all?
That's what I'm asking.
Maury|||yes.
Choose an OLEDB Provider and do not use a DSN.
OLEDB is going to be faster particularly when you choose the native provider
for your target platform (Example..."SQL Server")
Greg Jackson
PDX, Oregon|||In addition tot he other comments:
ODBC is a programming API. A low-level API. There exists higher level abstractions such as the RDO
object model on top of ODBC. ODBC uses a driver to connect to the DBMS.
OLEDB is a programming API. A low-level API. There exists higher level abstractions such as the ADO
object model on top of OLEDB. OLEDB uses a driver to connect to the DBMS. There exists an OLEDB
driver which sits on top of ODBC. This is probably what you are seeing.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods, but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury

Architecture advice

I need some advice on architecture.
I am a newbie to SQL Server, but a long time Access and FrontPage developer.
With these two apps, I have a "publish to the web" mentality, so I have
envisioned having my development SQL Server database on my local harddisk
and periodically "publishing" it to my production SQL Server out on the
Internet. (By development server, I mean where data creation and update is
accomplished.)
My idea: Update the production SQL Server periodically (weekly) from the
development server, but I need to capture data received from users on the
production server, then edit and merge on the development server for later
republishing to the production server. Alternatively, all data updates could
be done on the production server with backups to the local harddisk server.
Problem: My Internet SQL Server provider does NOT allow replication nor the
creation or implementation of DTS packages, but does support the DTS
import/export functions to a point of connection to and selection of my
database.
Which would be best approach to implement given the ISP's restrictions? Or
can you suggest another approach?
Many thanks for your expertise to point me in the right direction.
CharaxDo all your production changes on your production server,
and don't mix your Dev server with your prod server. If
you really need to do things first on your Dev server and
then publish 'it' to the prod server, you should then
treat your dev server as a prod server unless of course
by 'publishing' you meant scheduled releases in a
controlled fashion.
Linchi
>--Original Message--
>I need some advice on architecture.
>I am a newbie to SQL Server, but a long time Access and
FrontPage developer.
>With these two apps, I have a "publish to the web"
mentality, so I have
>envisioned having my development SQL Server database on
my local harddisk
>and periodically "publishing" it to my production SQL
Server out on the
>Internet. (By development server, I mean where data
creation and update is
>accomplished.)
>My idea: Update the production SQL Server periodically
(weekly) from the
>development server, but I need to capture data received
from users on the
>production server, then edit and merge on the development
server for later
>republishing to the production server. Alternatively, all
data updates could
>be done on the production server with backups to the
local harddisk server.
>Problem: My Internet SQL Server provider does NOT allow
replication nor the
>creation or implementation of DTS packages, but does
support the DTS
>import/export functions to a point of connection to and
selection of my
>database.
>Which would be best approach to implement given the ISP's
restrictions? Or
>can you suggest another approach?
>Many thanks for your expertise to point me in the right
direction.
>Charax
>
>.
>|||Thanks for the quick response, Linchi. Yes, you are quite right and my
terminology was wrong. (Remember, I'm an SQL server newbie!)
I now will call the server on my local harddisk the 'production' server. The
production server has the most current data and any changes to data or
database structure are made there. The ISP's SQL Server on the Internet is
simply a copy of the production server that web users can access -- let's
call it the 'slave'. Is this a reasonable architecture?
If so, how do you recommend that I update the slave server on the Internet
from the production server on local harddisk? Please keep in mind that the
slave server does not support replication, and only supports the DTS
import/export functions to a point of connection to and selection of my
database. On my local production server, I have all replication and DTS
features.
Many thanks for your ideas and help to a new guy.
Charax
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:339801c3e1c8$08021990$a601280a@.phx.gbl...
> Do all your production changes on your production server,
> and don't mix your Dev server with your prod server. If
> you really need to do things first on your Dev server and
> then publish 'it' to the prod server, you should then
> treat your dev server as a prod server unless of course
> by 'publishing' you meant scheduled releases in a
> controlled fashion.
> Linchisql

Architecture advice

I need some advice on architecture.
I am a newbie to SQL Server, but a long time Access and FrontPage developer.
With these two apps, I have a "publish to the web" mentality, so I have
envisioned having my development SQL Server database on my local harddisk
and periodically "publishing" it to my production SQL Server out on the
Internet. (By development server, I mean where data creation and update is
accomplished.)
My idea: Update the production SQL Server periodically (weekly) from the
development server, but I need to capture data received from users on the
production server, then edit and merge on the development server for later
republishing to the production server. Alternatively, all data updates could
be done on the production server with backups to the local harddisk server.
Problem: My Internet SQL Server provider does NOT allow replication nor the
creation or implementation of DTS packages, but does support the DTS
import/export functions to a point of connection to and selection of my
database.
Which would be best approach to implement given the ISP's restrictions? Or
can you suggest another approach?
Many thanks for your expertise to point me in the right direction.
CharaxDo all your production changes on your production server,
and don't mix your Dev server with your prod server. If
you really need to do things first on your Dev server and
then publish 'it' to the prod server, you should then
treat your dev server as a prod server unless of course
by 'publishing' you meant scheduled releases in a
controlled fashion.
Linchi

quote:

---

>--Original Message--
>I need some advice on architecture.
>I am a newbie to SQL Server, but a long time Access and

---

FrontPage developer.

quote:

---

>With these two apps, I have a "publish to the web"

---

mentality, so I have

quote:

---

>envisioned having my development SQL Server database on

---

my local harddisk

quote:

---

>and periodically "publishing" it to my production SQL

---

Server out on the

quote:

---

>Internet. (By development server, I mean where data

---

creation and update is

quote:

---

>accomplished.)
>My idea: Update the production SQL Server periodically

---

(weekly) from the

quote:

---

>development server, but I need to capture data received

---

from users on the

quote:

---

>production server, then edit and merge on the development

---

server for later

quote:

---

>republishing to the production server. Alternatively, all

---

data updates could

quote:

---

>be done on the production server with backups to the

---

local harddisk server.

quote:

---

>Problem: My Internet SQL Server provider does NOT allow

---

replication nor the

quote:

---

>creation or implementation of DTS packages, but does

---

support the DTS

quote:

---

>import/export functions to a point of connection to and

---

selection of my

quote:

---

>database.
>Which would be best approach to implement given the ISP's

---

restrictions? Or

quote:

---

>can you suggest another approach?
>Many thanks for your expertise to point me in the right

---

direction.

quote:

---

>Charax
>
>.
>

---

|||Thanks for the quick response, Linchi. Yes, you are quite right and my
terminology was wrong. (Remember, I'm an SQL server newbie!)
I now will call the server on my local harddisk the 'production' server. The
production server has the most current data and any changes to data or
database structure are made there. The ISP's SQL Server on the Internet is
simply a copy of the production server that web users can access -- let's
call it the 'slave'. Is this a reasonable architecture?
If so, how do you recommend that I update the slave server on the Internet
from the production server on local harddisk? Please keep in mind that the
slave server does not support replication, and only supports the DTS
import/export functions to a point of connection to and selection of my
database. On my local production server, I have all replication and DTS
features.
Many thanks for your ideas and help to a new guy.
Charax
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:339801c3e1c8$08021990$a601280a@.phx.gbl...

quote:

---

> Do all your production changes on your production server,
> and don't mix your Dev server with your prod server. If
> you really need to do things first on your Dev server and
> then publish 'it' to the prod server, you should then
> treat your dev server as a prod server unless of course
> by 'publishing' you meant scheduled releases in a
> controlled fashion.
> Linchi

---

application with critical database

Dear all

I am a pretty new in the development world fresh from uni. I am doing development on a system that has a security database. Access to the data in that database is pretty important. So in case the main server where the database is stored for soem reason fails or gets compromised i need to have a second copy with the most recent data in that database and keep the application up and running. The data i have is stored in a SQL 2005 database. What are the recomended aproaches for acheiving this needed reliability?

Would running the SQL Agent every 2 minutes do the trick? And replicate the database to another server and then have asecondary deployment on that server running as a backup? Or are there any other means?

Any advice is apreciated.

Sincerely

See if below links can be useful.

http://msdn2.microsoft.com/en-us/library/ms151247.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Application roles, good or bad?

We are trying to determine if using application roles would be the best
method to access our SQL database with a "to be developed" ASP.Net
application or possibly an Access project. Some of the requirements of the
application will require different users to have access to different parts
of the database. Some users may be able to modify data while other users
might be read only users. I assume that this would require the application
to use different application roles depending on the user that is logging
into the application?
Another requirement of the application is the ability to maintain an audit
trail for users. So, either we will still have to use the user account to
create the initial connection to the database before applying the
application role or the user name will have to be passed in by the
application so that it can be used for auditing if another (single) account
is used for the initial connection to the database. Are there any guidelines
for best practice or recommended practice? Thanks.
Paul Bauer
paul.bauer@.rimrockgroup.com
www.rimrockgroup.comThere's some good info on role base authentication at the following website;
Building Secure ASP.NET Applications: Authentication, Authorization, and
Secure Communication
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/SecNetch03.asp
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||From what you described it doesn't look like using application roles will
fit your model.
When an application role is activated for a connection by the application,
the connection
permanently loses all permissions applied to the login, user account, or
other groups or
database roles in all databases for the duration of the connection. The
connection gains the
permissions associated with the application role for the database in which
the application role exists.
This means all users who connects the db through this application will have
the same permissions in
this db (unless your implement your own logic inside the application, which
doesn't seems to be your goal).
Using Windows authentification seems to be better solution here.
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclaimer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Paul Bauer" <paul.bauer@.rimrockgroup.com> wrote in message
news:#z$3c0zTEHA.2580@.TK2MSFTNGP12.phx.gbl...
> We are trying to determine if using application roles would be the best
> method to access our SQL database with a "to be developed" ASP.Net
> application or possibly an Access project. Some of the requirements of the
> application will require different users to have access to different parts
> of the database. Some users may be able to modify data while other users
> might be read only users. I assume that this would require the application
> to use different application roles depending on the user that is logging
> into the application?
> Another requirement of the application is the ability to maintain an audit
> trail for users. So, either we will still have to use the user account to
> create the initial connection to the database before applying the
> application role or the user name will have to be passed in by the
> application so that it can be used for auditing if another (single)
account
> is used for the initial connection to the database. Are there any
guidelines
> for best practice or recommended practice? Thanks.
> Paul Bauer
> paul.bauer@.rimrockgroup.com
> www.rimrockgroup.com
>
>

Application Roles and Bulk Insert

Is it possible to grant a user access to Bulk Insert via an Application Role
?
I have a situation where I dont want to grant users direct access to any
tables, but there is one table that they will need to do a bulk insert into.
I cant see anywhere that you can assign an approle as part of bulkadmin.
Any ideas of how to do this would be appreciated. Thanks.I don't believe you can use "Application roles" for this, but you can assign
"Bulk Insert Administrator" Server Role to any individual Server Login...
Under SQL Server Login Properties, Server Roles, one of the Server Roles is
Bulk insert Administrator.
"Jace" wrote:

> Is it possible to grant a user access to Bulk Insert via an Application Ro
le?
> I have a situation where I dont want to grant users direct access to any
> tables, but there is one table that they will need to do a bulk insert int
o.
> I cant see anywhere that you can assign an approle as part of bulkadmin.
> Any ideas of how to do this would be appreciated. Thanks.

Application Roles across databases in SQL Server 2000

Hello
I have 2 databases that run application role security
(different role names and passwords), users access these
databases only from within different Visual Basic
applications.
I require to be able to request data from both
databases. I have read in SQL Server help that if you
enable the guest user account and then give it the
relevant permissions the system will only allow the other
database to get to these objects.
I have created a stored procedure on one of the databases
that calls a table in the database with the guest account
enabled. I have not given the guest account access to
this table but I can still get to the data in the table.
Please can someone explain why this is and what I need to
do to prevent this.
Thank you
Caroline> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
This is due to ownership chaining behavior. As long as all objects are
owned by the same login, permissions are not checked on indirectly
referenced objects. Additionally, you need to enable cross database
chaining for ownership chains to apply to cross-database access. This
appears to be the case in your environment.
As long as you access data only via views and procedures, you don't need to
grant any permissions to guest. This allows you to leverage ownership
chains as a security mechanism. See Ownership Chains in the Books Online
for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Caroline" <anonymous@.discussions.microsoft.com> wrote in message
news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
> Hello
> I have 2 databases that run application role security
> (different role names and passwords), users access these
> databases only from within different Visual Basic
> applications.
> I require to be able to request data from both
> databases. I have read in SQL Server help that if you
> enable the guest user account and then give it the
> relevant permissions the system will only allow the other
> database to get to these objects.
> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
> Thank you
> Caroline|||> and what I need to do to prevent this.
Only grant execute permissions on the procedure to those users/roles whom
you want to access the underlying data.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23fcOSPDYEHA.3596@.tk2msftngp13.phx.gbl...
> This is due to ownership chaining behavior. As long as all objects are
> owned by the same login, permissions are not checked on indirectly
> referenced objects. Additionally, you need to enable cross database
> chaining for ownership chains to apply to cross-database access. This
> appears to be the case in your environment.
> As long as you access data only via views and procedures, you don't need
to
> grant any permissions to guest. This allows you to leverage ownership
> chains as a security mechanism. See Ownership Chains in the Books Online
> for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Caroline" <anonymous@.discussions.microsoft.com> wrote in message
> news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
>

Application role to access xp_cmdshell

I have an Access app linked to a SQL server db. This app uses
xp_cmdshell in some stored procedures and it works fine. As long as
the user is administrator... I'd like to set up an application role
that can execute xp_cmdshell and access my db but I don't know how to
do it as xp_cmdshell is in the Master db while everything else is in
my own db. I'm also unsure whether to call sp_setapprole from the sp's
or from the Access app.
Can somebody please give me some code examples or direct me to a good
site?
/CarlAs long as the ownership chain is unbroken, direct permissions on
xp_cmdshell are not needed. This necessitates that your user procs be owned
by 'dbo', your user database be owned by 'sa' and cross-database chaining
(intoduced in SQL 2000 SP3) be enabled. Example script below.
For security reasons, it is important that your user proc be coded in such a
way that only the intended command can be executed. Also, you should enable
cross-database chaining only if you fully trust users that have permissions
to create dbo-owned objects. See Cross-database chaining in the SQL 2000
Books Online for more information.
You will also need to allow non-sysadmin users to execute xp_cmdshell. You
can do this from Enterprise Manager under Management-->SQL Server
Agent-->Job System. Uncheck the 'Only users with sysadmin privileges...'
check box and specify the Windows account you want to use as the OS security
context for non-sysadmin users. This account should have the minimal
permissions need to perform the needed tasks.

> I'm also unsure whether to call sp_setapprole from the sp's
> or from the Access app.
You'll need to execute sp_setapprole directly from your application. From
the Books Online:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_sp_sa-sz_6tt1.htm">
The sp_setapprole stored procedure can be executed only by direct
Transact-SQL statements; it cannot be executed within another stored
procedure or from within a user-defined transaction.
</Excerpt>
USE MyDatabase
EXEC sp_changedbowner 'sa'
GO
-- for SQL 2000 SP3+
EXEC sp_dboption 'MyDatabase', 'db chaining', true
GO
CREATE PROC dbo.MyXpCmdShellProc
AS
EXEC master..xp_cmdshell 'MyCommand'
GO
GRANT EXEC ON dbo.MyXpCmdShellProc TO MyAppRole
GO
EXEC sp_setapprole 'MyAppRole', 'MyAppRolePassword'
EXEC dbo.MyXpCmdShellProc
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Carl Olsson" <caos@.regerar.com> wrote in message
news:d495b147.0402170439.d8b1453@.posting.google.com...
> I have an Access app linked to a SQL server db. This app uses
> xp_cmdshell in some stored procedures and it works fine. As long as
> the user is administrator... I'd like to set up an application role
> that can execute xp_cmdshell and access my db but I don't know how to
> do it as xp_cmdshell is in the Master db while everything else is in
> my own db. I'm also unsure whether to call sp_setapprole from the sp's
> or from the Access app.
> Can somebody please give me some code examples or direct me to a good
> site?
> /Carl|||Thanks Dan for your excellent explanation. But unfortunately I'm still
at SQL 7... Any other options?
Carl
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<#kQj98V9DHA.3176@.TK
2MSFTNGP11.phx.gbl>...
> As long as the ownership chain is unbroken, direct permissions on
> xp_cmdshell are not needed. This necessitates that your user procs be own
ed
> by 'dbo', your user database be owned by 'sa' and cross-database chaining
> (intoduced in SQL 2000 SP3) be enabled. Example script below.
>|||The technique will work with SQL 7 too. The only difference is that
cross-database chaining is not configurable under SQL 7 and pre-SQL2000 SP3
(it is always on). Just remember to run the 'db chaining' option on in your
user database if you later upgrade to SQL 2000 SP3+.
Hope this helps.
Dan Guzman
SQL Server MVP
"Carl Olsson" <caos@.regerar.com> wrote in message
news:d495b147.0402172316.6a6cf8b@.posting.google.com...
> Thanks Dan for your excellent explanation. But unfortunately I'm still
> at SQL 7... Any other options?
> Carl
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<#kQj98V9DHA.3176@.TK2MSFTNGP11.phx.gbl>...
owned
chaining

Application Role

Hi,
I have an Access 2003 project which uses a "dummy" login for the standard
connection without any rights on objects. After establishing the ADO
connection via VBA code I enable the application role which is then used to
do all database access
cn.Execute "EXEC sp_setapprole 'App_Role', '" & strPW & "'"
This works fine unless I assign a rowsource via a code line like this
...Form.RecordSource = "Exec GET_" & Str_SubFormName & " " & strWhere
This works only when I assign the dummy login the right to execute the
stored procedure. It is not enough to assign this right to the Application
role. But this means that the dummy role has access to this table via the
stored procedure which I wanted to avoid. What do I miss, or is this by
design? Any other solutions for this scenario?
TIA,
Norbert MeissNorbert Meiss (NorbertMeiss@.discussions.microsoft.com) writes:
> I have an Access 2003 project which uses a "dummy" login for the
> standard connection without any rights on objects. After establishing
> the ADO connection via VBA code I enable the application role which is
> then used to do all database access
> cn.Execute "EXEC sp_setapprole 'App_Role', '" & strPW & "'"
> This works fine unless I assign a rowsource via a code line like this
> ...Form.RecordSource = "Exec GET_" & Str_SubFormName & " " & strWhere
> This works only when I assign the dummy login the right to execute the
> stored procedure. It is not enough to assign this right to the Application
> role. But this means that the dummy role has access to this table via the
> stored procedure which I wanted to avoid. What do I miss, or is this by
> design? Any other solutions for this scenario?
Since I don't know Access, I will have to guess a little here. But I have
a feeling that the RecordSource stored procedure gets executed on a second
connection to SQL Server. In such case, that connection will not have the
application role set.
Why you get this second connection, I don't know. It could be inherent
with the Form.RecordSource thing, and thus no way to avoid it. But there
is also an issue with ADO. If you fail to get all data from a query, some
client libraries will tell you that the connection is busy, and no new
query can be submitted. But ADO tries to be a nice guy, and opens a new
connection to SQL Server and submits the qurey on that connection. This
often causes more problem than it solves. Really to avoid this, I don't
know, since I know nothing about your code. But "SET NOCOUNT ON" saves
the day in many cases, since the result sets you have not consumed,
often are rowcounts from INSERT/UPDATE/DELETE statements.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I cannot reproduce the strange behaviour from my first post - maybe I was
wrong with my observations. The SET NOCOUNT ON hint is helpful anyway. I
didn't understand its meaning so far. Thank you.
Norbert Meiss
"Erland Sommarskog" wrote:

> Norbert Meiss (NorbertMeiss@.discussions.microsoft.com) writes:
> Since I don't know Access, I will have to guess a little here. But I have
> a feeling that the RecordSource stored procedure gets executed on a second
> connection to SQL Server. In such case, that connection will not have the
> application role set.
> Why you get this second connection, I don't know. It could be inherent
> with the Form.RecordSource thing, and thus no way to avoid it. But there
> is also an issue with ADO. If you fail to get all data from a query, some
> client libraries will tell you that the connection is busy, and no new
> query can be submitted. But ADO tries to be a nice guy, and opens a new
> connection to SQL Server and submits the qurey on that connection. This
> often causes more problem than it solves. Really to avoid this, I don't
> know, since I know nothing about your code. But "SET NOCOUNT ON" saves
> the day in many cases, since the result sets you have not consumed,
> often are rowcounts from INSERT/UPDATE/DELETE statements.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||In addition to what Erland said, you are going to have intermittent
problems using application roles with Access unless you turn off
connection pooling in the connection string and test to make sure that
the application role is active before you execute any EXEC statements.
This is all going to add overhead to the app and slow it down somewhat
-- how much depends on the app and number of users. It is generally
recommended NOT to use application roles with Access because it opens
and closes connections under the covers. See
http://support.microsoft.com/defaul...B;EN-US;Q229564 for
more info on the connection pooling issue.
--Mary
On Mon, 27 Dec 2004 00:19:03 -0800, "Norbert Meiss"
<NorbertMeiss@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I cannot reproduce the strange behaviour from my first post - maybe I was
>wrong with my observations. The SET NOCOUNT ON hint is helpful anyway. I
>didn't understand its meaning so far. Thank you.
>Norbert Meiss
>"Erland Sommarskog" wrote:
>|||Thanks Mary.
While performance is not an issue so far with some 25 users and a big server
I will give it a second thougt and some testing.
Norbert
"Mary Chipman" wrote:

> In addition to what Erland said, you are going to have intermittent
> problems using application roles with Access unless you turn off
> connection pooling in the connection string and test to make sure that
> the application role is active before you execute any EXEC statements.
> This is all going to add overhead to the app and slow it down somewhat
> -- how much depends on the app and number of users. It is generally
> recommended NOT to use application roles with Access because it opens
> and closes connections under the covers. See
> http://support.microsoft.com/defaul...B;EN-US;Q229564 for
> more info on the connection pooling issue.
> --Mary
> On Mon, 27 Dec 2004 00:19:03 -0800, "Norbert Meiss"
> <NorbertMeiss@.discussions.microsoft.com> wrote:
>
>|||A good tool to use when testing is Profiler, which allows you to
eavesdrop on the actual conversation Access is having with the SQL
Server. It's interesting to see what's going on under the covers when
it comes to troubleshooting existing problems and avoiding potential
ones.
--Mary
On Mon, 27 Dec 2004 06:39:04 -0800, "Norbert Meiss"
<NorbertMeiss@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks Mary.
>While performance is not an issue so far with some 25 users and a big serve
r
>I will give it a second thougt and some testing.
>Norbert
>"Mary Chipman" wrote:
>

Application needs replication

Hi
We have an MS Access application which runs on the server but some laptop
users need the app to work offline. Our solution has been to use Access
replication which automatically syncs data (both ways) when laptop is
connected to the network. We are looking to rewrite the app to vb.net/sql
server. My question is how does replication work in sql server specially in
context of a vb.net front end i.e. what sort of coding/configuration we are
we looking at?
Thanks
RegardsWell since you will have disconnected users; you will need to have a local
instance of the SQL Server installed on the computer. Then you can SQL
Server 2005 Merge Replication Topology to do what yaa needed. Read up on it
at ..
http://technet.microsoft.com/en-us/sqlserver/bb331775.aspx
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John" wrote:
> Hi
> We have an MS Access application which runs on the server but some laptop
> users need the app to work offline. Our solution has been to use Access
> replication which automatically syncs data (both ways) when laptop is
> connected to the network. We are looking to rewrite the app to vb.net/sql
> server. My question is how does replication work in sql server specially in
> context of a vb.net front end i.e. what sort of coding/configuration we are
> we looking at?
> Thanks
> Regards
>
>|||Am I right to believe I need to code as if my app is connected to the same
single sql server and various sql server installs would automatically take
care of the sync between themselves without intervention by app when laptops
are connected to the network?
Thanks
Regards
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:D4FD10E6-2DF6-486A-B6F9-88000C5A076A@.microsoft.com...
> Well since you will have disconnected users; you will need to have a local
> instance of the SQL Server installed on the computer. Then you can SQL
> Server 2005 Merge Replication Topology to do what yaa needed. Read up on
> it
> at ..
> http://technet.microsoft.com/en-us/sqlserver/bb331775.aspx
> Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "John" wrote:
>> Hi
>> We have an MS Access application which runs on the server but some laptop
>> users need the app to work offline. Our solution has been to use Access
>> replication which automatically syncs data (both ways) when laptop is
>> connected to the network. We are looking to rewrite the app to vb.net/sql
>> server. My question is how does replication work in sql server specially
>> in
>> context of a vb.net front end i.e. what sort of coding/configuration we
>> are
>> we looking at?
>> Thanks
>> Regards
>>|||In a sense yes, but actually no. What I mean is, since you will have a
local install of SQL Server on each laptop; you can code the application to
connect to localhost and no coding required for developer for connecting
parts. But in fact; these are all different instances of SQL Server on
different computer, everyone will have different names.
I haven't coded for replication enviornment, but few challanges that face
you developers is handling merge conflicts. Three possible cases can happen:
1) Record is updated, but no longer exists.
2) Insert a record; and causes a duplicate primary key.
3) Updating same records in two locations, both valid but are in conflict.
Replication is usually timed event; so onces the server is on the network,
the local server will talk to the distributor and the publitioning database
to update information as needed. I hope this gives you some ideas. Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John" wrote:
> Am I right to believe I need to code as if my app is connected to the same
> single sql server and various sql server installs would automatically take
> care of the sync between themselves without intervention by app when laptops
> are connected to the network?
> Thanks
> Regards
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:D4FD10E6-2DF6-486A-B6F9-88000C5A076A@.microsoft.com...
> > Well since you will have disconnected users; you will need to have a local
> > instance of the SQL Server installed on the computer. Then you can SQL
> > Server 2005 Merge Replication Topology to do what yaa needed. Read up on
> > it
> > at ..
> > http://technet.microsoft.com/en-us/sqlserver/bb331775.aspx
> >
> > Thanks!
> > --
> > Mohit K. Gupta
> > B.Sc. CS, Minor Japanese
> > MCTS: SQL Server 2005
> >
> >
> > "John" wrote:
> >
> >> Hi
> >>
> >> We have an MS Access application which runs on the server but some laptop
> >> users need the app to work offline. Our solution has been to use Access
> >> replication which automatically syncs data (both ways) when laptop is
> >> connected to the network. We are looking to rewrite the app to vb.net/sql
> >> server. My question is how does replication work in sql server specially
> >> in
> >> context of a vb.net front end i.e. what sort of coding/configuration we
> >> are
> >> we looking at?
> >>
> >> Thanks
> >>
> >> Regards
> >>
> >>
> >>
>
>|||John,
You have at least two possiblilties, use a replication of the SQL server
using by instance SQL Client, or make your application in a way that it uses
DataSets from the needed information.
The latter is very easy to do and the way I would go in your situation.
Cor|||And you don't need SQL Server installed on the client(s). Another
alternative is SQL Server Compact Edition on the clients. It can also act as
a Subscriber to a SQL Server Publisher. Note that a SQL Server Publisher
must be Workgroup or better--SQL Express does not support this
functionality. In addition, you might consider using the new ADO.NET 3.5
Sync Services due out with Orcas (before the end of the year). In this case
you don't need SQL Server to act as a Publisher.
I discuss most of these options in my Ebook on SQL CE.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"John" <John@.nospam.infovis.co.uk> wrote in message
news:O4l0ZMECIHA.324@.TK2MSFTNGP04.phx.gbl...
> Hi
> We have an MS Access application which runs on the server but some laptop
> users need the app to work offline. Our solution has been to use Access
> replication which automatically syncs data (both ways) when laptop is
> connected to the network. We are looking to rewrite the app to vb.net/sql
> server. My question is how does replication work in sql server specially
> in context of a vb.net front end i.e. what sort of coding/configuration we
> are we looking at?
> Thanks
> Regards
>|||Hi Cor
Thanks. How does one keep data persistent on client side when using
datasets, bearing in mind that clients (laptops) will be disconnected from
the network/sql server often but would need access to sql server data
offline? If you can point to some reading that would be great.
Many Thanks
Regards
"Cor Ligthert[MVP]" <notmyfirstname@.planet.nl> wrote in message
news:14F13D20-64C8-46CB-BEF1-CC700A4A6474@.microsoft.com...
> John,
> You have at least two possiblilties, use a replication of the SQL server
> using by instance SQL Client, or make your application in a way that it
> uses DataSets from the needed information.
> The latter is very easy to do and the way I would go in your situation.
> Cor
>|||Take a look at Sybase SQL Anywhere. It's replication capabilities are truly
amazing.
"John" <John@.nospam.infovis.co.uk> wrote in message
news:O4l0ZMECIHA.324@.TK2MSFTNGP04.phx.gbl...
> Hi
> We have an MS Access application which runs on the server but some laptop
> users need the app to work offline. Our solution has been to use Access
> replication which automatically syncs data (both ways) when laptop is
> connected to the network. We are looking to rewrite the app to vb.net/sql
> server. My question is how does replication work in sql server specially
> in context of a vb.net front end i.e. what sort of coding/configuration we
> are we looking at?
> Thanks
> Regards
>|||John,
The nature from a dataset is to work ofline, it is build for that.
When you do an update, there is looked if there has been a change (this is
called optimistic concurrency). The way as the data is organised makes that
the chanch for that is low or high.
By instance as you are adding and subtrackting values from tablerows,
instead of adding mutation rows, then you can probably forget it.
Cor|||Ah, I expect he means you can serialize the DataTable(s) to an XML file or
somesuch.
Again, the SQL Server Compact Edition (which supports several kinds of
replication) is a better option.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Cor Ligthert[MVP]" <notmyfirstname@.planet.nl> wrote in message
news:522F9B5D-D648-4D7B-99F1-58EAB1B33923@.microsoft.com...
> John,
> The nature from a dataset is to work ofline, it is build for that.
> When you do an update, there is looked if there has been a change (this is
> called optimistic concurrency). The way as the data is organised makes
> that the chanch for that is low or high.
> By instance as you are adding and subtrackting values from tablerows,
> instead of adding mutation rows, then you can probably forget it.
> Cor|||Thanks Bill.
Regards
"William Vaughn" <billvaNoSPAM@.betav.com> wrote in message
news:OYv3bNdCIHA.6012@.TK2MSFTNGP03.phx.gbl...
> Ah, I expect he means you can serialize the DataTable(s) to an XML file
> or somesuch.
> Again, the SQL Server Compact Edition (which supports several kinds of
> replication) is a better option.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant, Dad, Grandpa
> Microsoft MVP
> INETA Speaker
> www.betav.com
> www.betav.com/blog/billva
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "Cor Ligthert[MVP]" <notmyfirstname@.planet.nl> wrote in message
> news:522F9B5D-D648-4D7B-99F1-58EAB1B33923@.microsoft.com...
>> John,
>> The nature from a dataset is to work ofline, it is build for that.
>> When you do an update, there is looked if there has been a change (this
>> is called optimistic concurrency). The way as the data is organised makes
>> that the chanch for that is low or high.
>> By instance as you are adding and subtrackting values from tablerows,
>> instead of adding mutation rows, then you can probably forget it.
>> Cor
>|||Bill,
I disagree this with you, for extra database you need replication, with a
dataset you just needs common updates. The logical problems stays exactly
the same.
The serialization is completely automaticaly done by Dataset.ReadXML(...)
and Dataset.WriteXML(...), you write it as if this is a problem.
Cor|||Cor, you missed the point. Replication in this case is used for data
sharing--not just data persistence.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Cor Ligthert[MVP]" <notmyfirstname@.planet.nl> wrote in message
news:C859F647-3DB1-404A-AC9E-10213DDFA7AC@.microsoft.com...
> Bill,
> I disagree this with you, for extra database you need replication, with a
> dataset you just needs common updates. The logical problems stays exactly
> the same.
> The serialization is completely automaticaly done by Dataset.ReadXML(...)
> and Dataset.WriteXML(...), you write it as if this is a problem.
> Cor|||> Cor, you missed the point. Replication in this case is used for data
> sharing--not just data persistence.
I did not miss that Bill, however why would you do that if there are now
simple methods possible in ADONET and not only replication. Using a dataset
is in my idea much easier to handle conflicts by your own program.
Cor

Application Login SQL Error

I created aASP.net 2.0 application usingC# on VS2005
The application access several database on a remoteSQL Server 2005

I recently added Login functionalties to the application, this created a MDF in the app_data folder.

Everything works fine on my local desk top...

I can access my remote SQL Server 2005

and the local MDF file works fine, I can create account, login and all that fun stuff

So I Published the site to my target server:

Which is the same server running the SQL Server 2005

The parts of the application that does not require login works fine, I can access the SQL server 2005 with ease..see data, update, everything

However when ever I try to login or create an account from the application(MDF file) I get this:

An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I can only assume that the problem is with the MDF file.

Can anyone point me in the right direction?

Thank you
Andre

This is often fixed by ensuring the Initial Catalog entry is in the connectionstring.

.....Initial Catalog=yourFileWithoutTheExt....

I just reread the error...actually, I think it's because you are trying to administer the DB from your PC, and it's not setup that way.

|||

Thank you for responding,

On my target server I do not have SQL Express but SQL server 2005 and the remote setting for that is correct.

I changed my application from using the local SQL Express MDF file to use the SQL server 2005 for membership and roles by running aspnet_sql.exe to set up the database.

And all is working.

Thanks again

Andre

Application insist open SQL Server 2005 while the host still with SQL Server 2000

I don't know why my application on the internet try to access the SQL Server 2005, when the host still having the 2000 edition. I put the following connection string:

Data Source=##DBSERVER##;Initial Catalog=##DBNAME##;User ID=##DBUSER##;Password=##DBPASSWORD##;

Isn't everything fine with that? I created the table by host but dosen't work. I'm wondering if can I conect straight with Data Base, without register in the server, like with mdb?

This is the error that I'm getting.

When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Hi,

I'd like to know if the ##DBSERVER## you refer to is a correct database name with the desired SQL Server 2000. If you have multiple instance of SQL Server installed on that server, in order to access the correct one, you need to specify the instance name, if that's not the default instance, like "Data Source=DBServer\InstanceName".

For this specific error message, you can also check the following KB article for troubleshooting.

http://support.microsoft.com/kb/914277/en-us

appending data from database 1 to database 2 (live into Dev env)

have 2 databases in sql server 2000

Live
Dev

I need to append all the data from LIVE into DEV environment. I have tried using MS Access (linking tables & importing tables and running APPEND query to update the rows from LIVE to DEV but PK & FK is causing problems as some data will have the same ID's...

Could I use DTS--Access wizard in SQL Server.

What is the best option to use?

Thanks all in advanceIf this is a one-time transfer, or if does not need to occur on a scheduled basis, you can just right click on your production database in Enterprise Manager and follow the wizard to export data.|||The best thing, IMHO, is to restore you production to development. It will force you to write a script to do a delta, and it will clean up your dev environment. If you don't have the space, buy it. It is cheap.

Appending a custom sql where clause

Hello,
I am in the process of evaluating SSRS 2005 to replace an home grown
reporting tool. In my reporting application all users have access all
the tables\fields in the database. In the home grown tool, data
security is implemented by the following mechanism. When users run
reports, a standard sql where clause is appended to the sql generated
by the reproting tool. This standard where clause has @.userID as the
parameter.
Now is there a way in SSRS 2005 I can append a standard where clause to
every report just before it is run? Does it have an event model, I can
hook into?
Thanks
_Gigi JKIn most cases query parameters are mapped to report parameters but they do
not have to be, they can be mapped to expressions. When mapping a query
parameter to an expression you can map the query parameter to a global
variable. One of the global variables available is User!UserID. This
variable has the user (and their domain). If you don't want the domain then
you would strip the domain off.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<gigijk@.gmail.com> wrote in message
news:1137098143.325204.240880@.g47g2000cwa.googlegroups.com...
> Hello,
> I am in the process of evaluating SSRS 2005 to replace an home grown
> reporting tool. In my reporting application all users have access all
> the tables\fields in the database. In the home grown tool, data
> security is implemented by the following mechanism. When users run
> reports, a standard sql where clause is appended to the sql generated
> by the reproting tool. This standard where clause has @.userID as the
> parameter.
> Now is there a way in SSRS 2005 I can append a standard where clause to
> every report just before it is run? Does it have an event model, I can
> hook into?
> Thanks
> _Gigi JK
>|||Bruce,
Thanks for the reply. Sorry I sent the same question to you directly from my
gmail address as well.
In my case, reporting application will be intergrated into another
application which does not use NT auth. Is there a way I could pass in userID
to reporting tool?
On the original issue, what I want to do is to automatically append a where
clause ( for eg: 'AND sysem_id IN (SELECT System_id FROM Mdu_system where
user_id=@.userID') to every query generated by the reporting tool withou the
user intervention. Would this be posssible.
Once again thank you.
_GJK
"Bruce L-C [MVP]" wrote:
> In most cases query parameters are mapped to report parameters but they do
> not have to be, they can be mapped to expressions. When mapping a query
> parameter to an expression you can map the query parameter to a global
> variable. One of the global variables available is User!UserID. This
> variable has the user (and their domain). If you don't want the domain then
> you would strip the domain off.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <gigijk@.gmail.com> wrote in message
> news:1137098143.325204.240880@.g47g2000cwa.googlegroups.com...
> > Hello,
> > I am in the process of evaluating SSRS 2005 to replace an home grown
> > reporting tool. In my reporting application all users have access all
> > the tables\fields in the database. In the home grown tool, data
> > security is implemented by the following mechanism. When users run
> > reports, a standard sql where clause is appended to the sql generated
> > by the reproting tool. This standard where clause has @.userID as the
> > parameter.
> >
> > Now is there a way in SSRS 2005 I can append a standard where clause to
> > every report just before it is run? Does it have an event model, I can
> > hook into?
> >
> > Thanks
> > _Gigi JK
> >
>
>|||You can have a hidden parameter (i.e. it does not prompt for it but you can
include it when running the report).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GJK" <GJK@.discussions.microsoft.com> wrote in message
news:D37B86F8-AA55-4FF1-BC01-C426D806AF02@.microsoft.com...
> Bruce,
> Thanks for the reply. Sorry I sent the same question to you directly from
> my
> gmail address as well.
> In my case, reporting application will be intergrated into another
> application which does not use NT auth. Is there a way I could pass in
> userID
> to reporting tool?
> On the original issue, what I want to do is to automatically append a
> where
> clause ( for eg: 'AND sysem_id IN (SELECT System_id FROM Mdu_system where
> user_id=@.userID') to every query generated by the reporting tool withou
> the
> user intervention. Would this be posssible.
> Once again thank you.
> _GJK
> "Bruce L-C [MVP]" wrote:
>> In most cases query parameters are mapped to report parameters but they
>> do
>> not have to be, they can be mapped to expressions. When mapping a query
>> parameter to an expression you can map the query parameter to a global
>> variable. One of the global variables available is User!UserID. This
>> variable has the user (and their domain). If you don't want the domain
>> then
>> you would strip the domain off.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <gigijk@.gmail.com> wrote in message
>> news:1137098143.325204.240880@.g47g2000cwa.googlegroups.com...
>> > Hello,
>> > I am in the process of evaluating SSRS 2005 to replace an home grown
>> > reporting tool. In my reporting application all users have access all
>> > the tables\fields in the database. In the home grown tool, data
>> > security is implemented by the following mechanism. When users run
>> > reports, a standard sql where clause is appended to the sql generated
>> > by the reproting tool. This standard where clause has @.userID as the
>> > parameter.
>> >
>> > Now is there a way in SSRS 2005 I can append a standard where clause to
>> > every report just before it is run? Does it have an event model, I can
>> > hook into?
>> >
>> > Thanks
>> > _Gigi JK
>> >
>>

Append query from Access Table to Linked SQL Server Table Failing

Strange one here - I am posting this in both SQL Server and Access forums

Access is telling me it can't append any of the records due to a key violation.

The query:

INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;

Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.

There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.

I can manually append the records using cut and paste with no problems.

I have tried re-linking the tables.

Any ideas?
Thanks,
BradI'd guess that the SQL Server db you think you are linking to in dbo_Colors isn't really the one you think. Perhaps the login/password in your datasource is connecting to a different database than the one you expect?

To check, get the name of the constraint being violated and check in the sql server table to see if that constraint exists

Also, try running the sql server profiler to see what sql server db ms access is trying to insert data into|||mattrevs,

It does appear that I am linking the right table. There is only one Colors table and only one database that has a colors table.

Could you tell me how to check which constraint is being violated? After I manually pasted the data into dbo_Colors, I ran checkconstraints() and no error were reported.

I also tried running the query with implicit_transaction OFF and still no joy.

I ran the trace and here are the last two lines. The first one was simply duplicated for each record:

RPC:Completed exec sp_executesql N'INSERT INTO "dbo"."Colors" ("NameColorID","Application","Red","Green","Blue") VALUES (@.P1,@.P2,@.P3,@.P4,@.P5)', N'@.P1 nvarchar(38),@.P2 nvarchar(30),@.P3 int,@.P4 int,@.P5 int', N'{FFC28EAD-1134-40BB-9723-7D88A0B0AC7A}', N'Tile1', 197, 183, 156 Microsoft Access sa 0 11 0 0 2564 54 2004-06-21 10:35:14.170

SQL:BatchCompleted IF @.@.TRANCOUNT > 0 ROLLBACK TRAN Microsoft Access sa 0 0 0 0 2564 54 2004-06-21 10:35:19.403

- Brad|||You say that pasting the data one row at a time from within ms access works ok?

If so, maybe you could also perform a sql trace on this and see what is different?|||I got it figured out. (BTW, I did the trace with the pasting but the results were ... confusing)

Apparently a bit field in SQL server can be Null?

There are two additional fields in the SQL version of the table. The NVarChar field I had set to allow nulls. I never bothered with the Bit field.

When I checked Allow Nulls on the bit field and re-linked, my query worked.

I guess my lesson her is never assume anything. I have learned that lesson many times and will probably learn it again in the (near) future.

Thanks for your help,
Brad

Anyway to Stop DSN & DSNless connections to SQL Server?

I'm trying to setup a new server but need to stop people accessing the database via Access, Excel etc

Is there any way to set SQL server up to stop connections from such applications?

Is there any way to allow only certain users to connect via a DSN

We are not using windows authentication on the server

Cheers

No, you cannot restrict connections based on the application that made them. But you can restrict connections based on the account they were made with. You can try to have your application connect to SQL Server using a specific account and not grant any connection rights to the users - then they would only be able to access the database through the application. Of course, if they would debug the application, they could figure out how it connects to SQL Server (the account and the password), but this would at least prevent accidental connections from other applications.

Thanks
Laurentiu