Access web application is slow, should I upgrade to SQL server?

Hi,
first time poster/newbie here.
I've got a football (soccer for the yanks!) predictions league website that is driven by and Access database. It basically calculates points scored for a user getting certain predictions correct. This is the URL:
http://www.pool-predictions.co.uk/home/index.asp
There are two sections of the site however that have almost ground to halt now that more users have registered throught the season. The players section and league table section have gone progressively slower to load throughout the year and almost taking 2 minutes to load.
http://www.pool-predictions.co.uk/home/players.asp?tab=a_d
http://www.pool-predictions.co.uk/home/table.asp
All the calculations are performed in the Access database Ive written and there are Access SQL queries to get the data out.
My question is, is how can I speed the bloody thing up! ! Somone has alos suggested to me that I use stored procedures and SQL Server to speed things up? Ive never used SQL Server before so I am bit scared about using it (Im only a hobbyist), and I dont even know what a SP is or does. How easy will it be upgrading the whole thing to SQL Server and will it be worth the hassle, bearing in mind I expect my userbase to keep growing? Do SP help speed things up significantly? Would appreciate some advice!
Thanks in advance,
John.

This is one of those how long is a piece of string questions....

In general I woudl recomend Access Db apps with more than 5/20 users upgrade to a version of SQL Server.

An SP is a way of capturing logic and running it inside the server, if your app brings back lots of data and then processes in the web page then its likely that an SP will be faster, but it depends on the logic. Its faster because you can be more selective with the data, you are not pullng a lot of unused data over the wire etc.

Do you know which parts of the app are slow? specific queries? you could try running it ona local machine using both access and sqlexpress and compare the perf to see.

Access to SQL Server via WCF works only part time

We have 2 databases ( Guider and Talker ) and we have a WCF service that is logged in with a domain identity.

In our SQL Server we have the service ID added to the Data Server Logins and both Guider and Talker are given access to the user.

When we access Guider we have no problems getting data.

When we access Talker we have a login failure:

Cannot open database 'Talker' requested by the login. The login failed.

Login failed for user 'Acorn\CommunicationServices'.

The thing that gets me is that the user is created at the Server level, in both Databases, and at the server level both databases are checked for the user. master has been set as the default database for the user.

Basically, as far as I can see Talker and Guider are configured identically! So I cannot figure out why I cannot login to the second database!

Is there a specific setting I'm missing somewhere to grant login access to the user? I'm using

Management Studio Express to manage the database.

My guess here is that the default database defined for the login that fails (Acorn\CommunicationServices) is not configured properly. Another possibility may be a typo when specifying the DB name in the client.

One test you can try to verify if the principal can really access the DB is:

*Connect as a sysadmin

* run “EXECUTE AS LOGIN = ‘login_name’ “ to impersonate the principal

* USE [Talker]

I hope this helps,

-Raul Garcia

SDE/T

SQL Server Engine

Access to SQL Server via WCF works only part time

We have 2 databases ( Guider and Talker ) and we have a WCF service that is logged in with a domain identity.

In our SQL Server we have the service ID added to the Data Server Logins and both Guider and Talker are given access to the user.

When we access Guider we have no problems getting data.

When we access Talker we have a login failure:

Cannot open database 'Talker' requested by the login. The login failed.

Login failed for user 'Acorn\CommunicationServices'.

The thing that gets me is that the user is created at the Server level, in both Databases, and at the server level both databases are checked for the user. master has been set as the default database for the user.

Basically, as far as I can see Talker and Guider are configured identically! So I cannot figure out why I cannot login to the second database!

Is there a specific setting I'm missing somewhere to grant login access to the user? I'm using

Management Studio Express to manage the database.

My guess here is that the default database defined for the login that fails (Acorn\CommunicationServices) is not configured properly. Another possibility may be a typo when specifying the DB name in the client.

One test you can try to verify if the principal can really access the DB is:

*Connect as a sysadmin

* run “EXECUTE AS LOGIN = ‘login_name’ “ to impersonate the principal

* USE [Talker]

I hope this helps,

-Raul Garcia

SDE/T

SQL Server Engine

Access to SQL

Hello, How can I turn the following Access query into a SQL query?
Every time I try, I get Cartesian product
SELECT dbo_CLIENT.CLIENT_NUMBER, dbo_CLIENT.LNAME1, dbo_CLIENT.FNAME1,
dbo_CLIENT.INIT1, dbo_CLIENT.LNAME2, dbo_CLIENT.FNAME2, dbo_CLIENT.INIT2,
dbo_ADDRESS.ADDRESS1, dbo_ADDRESS.ADDRESS2, dbo_ADDRESS.ADDRESS3,
dbo_ADDRESS.CITY, dbo_ADDRESS.STATE, dbo_ADDRESS.ZIPCODE, dbo_ADDRESS.COUNTR
Y
FROM ((QNameAddress1 INNER JOIN dbo_CLIENT ON QNameAddress1.CLIENT_NUMBER =
dbo_CLIENT.CLIENT_NUMBER) INNER JOIN dbo_ADDRXREF ON
(QNameAddress1.MaxOfPOLICY_DATE_TIME = dbo_ADDRXREF.POLICY_DATE_TIME) AND
(QNameAddress1.POLICY_NUMBER = dbo_ADDRXREF.POLICY_NUMBER)) INNER JOIN
dbo_ADDRESS ON (dbo_ADDRXREF.SEQUENCE_NUMBER = dbo_ADDRESS.SEQUENCE_NUMBER)
AND (dbo_CLIENT.CLIENT_NUMBER = dbo_ADDRESS.CLIENT_NUMBER)
WHERE (((dbo_ADDRXREF.ADDR_USAGE)="1"));Hi
This may be a data issue
Your query looks fine:
SELECT C.CLIENT_NUMBER,
C.LNAME1,
C.FNAME1,
C.INIT1,
C.LNAME2,
C.FNAME2,
C.INIT2,
A.ADDRESS1,
A.ADDRESS2,
A.ADDRESS3,
A.CITY,
A.STATE,
A.ZIPCODE,
A.COUNTRY
FROM QNameAddress1 Q
JOIN dbo_CLIENT C ON Q.CLIENT_NUMBER = C.CLIENT_NUMBER
JOIN dbo_ADDRXREF X ON Q.MaxOfPOLICY_DATE_TIME = X.POLICY_DATE_TIME AND
Q.POLICY_NUMBER = X.POLICY_NUMBER
JOIN dbo_ADDRESS A ON X.SEQUENCE_NUMBER = A.SEQUENCE_NUMBER AND
C.CLIENT_NUMBER = A.CLIENT_NUMBER
WHERE A.ADDR_USAGE='1'
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
example data (as insert statements). It may also be worth posting expected
output from sample data.
John
"Patrice" wrote:

> Hello, How can I turn the following Access query into a SQL query?
> Every time I try, I get Cartesian product
>
> SELECT dbo_CLIENT.CLIENT_NUMBER, dbo_CLIENT.LNAME1, dbo_CLIENT.FNAME1,
> dbo_CLIENT.INIT1, dbo_CLIENT.LNAME2, dbo_CLIENT.FNAME2, dbo_CLIENT.INIT2,
> dbo_ADDRESS.ADDRESS1, dbo_ADDRESS.ADDRESS2, dbo_ADDRESS.ADDRESS3,
> dbo_ADDRESS.CITY, dbo_ADDRESS.STATE, dbo_ADDRESS.ZIPCODE, dbo_ADDRESS.COUN
TRY
> FROM ((QNameAddress1 INNER JOIN dbo_CLIENT ON QNameAddress1.CLIENT_NUMBER
=
> dbo_CLIENT.CLIENT_NUMBER) INNER JOIN dbo_ADDRXREF ON
> (QNameAddress1.MaxOfPOLICY_DATE_TIME = dbo_ADDRXREF.POLICY_DATE_TIME) AND
> (QNameAddress1.POLICY_NUMBER = dbo_ADDRXREF.POLICY_NUMBER)) INNER JOIN
> dbo_ADDRESS ON (dbo_ADDRXREF.SEQUENCE_NUMBER = dbo_ADDRESS.SEQUENCE_NUMBER
)
> AND (dbo_CLIENT.CLIENT_NUMBER = dbo_ADDRESS.CLIENT_NUMBER)
> WHERE (((dbo_ADDRXREF.ADDR_USAGE)="1"));
>
>|||Plug that into the Query Analyzer and you should find the problem. Most
likely some or all of your INNER JOINS should be OUTER.
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:0D946FD4-B400-482F-8510-1AA01E1432BD@.microsoft.com...
> Hello, How can I turn the following Access query into a SQL query?
> Every time I try, I get Cartesian product
>
> SELECT dbo_CLIENT.CLIENT_NUMBER, dbo_CLIENT.LNAME1, dbo_CLIENT.FNAME1,
> dbo_CLIENT.INIT1, dbo_CLIENT.LNAME2, dbo_CLIENT.FNAME2, dbo_CLIENT.INIT2,
> dbo_ADDRESS.ADDRESS1, dbo_ADDRESS.ADDRESS2, dbo_ADDRESS.ADDRESS3,
> dbo_ADDRESS.CITY, dbo_ADDRESS.STATE, dbo_ADDRESS.ZIPCODE,
> dbo_ADDRESS.COUNTRY
> FROM ((QNameAddress1 INNER JOIN dbo_CLIENT ON QNameAddress1.CLIENT_NUMBER
> =
> dbo_CLIENT.CLIENT_NUMBER) INNER JOIN dbo_ADDRXREF ON
> (QNameAddress1.MaxOfPOLICY_DATE_TIME = dbo_ADDRXREF.POLICY_DATE_TIME) AND
> (QNameAddress1.POLICY_NUMBER = dbo_ADDRXREF.POLICY_NUMBER)) INNER JOIN
> dbo_ADDRESS ON (dbo_ADDRXREF.SEQUENCE_NUMBER =
> dbo_ADDRESS.SEQUENCE_NUMBER)
> AND (dbo_CLIENT.CLIENT_NUMBER = dbo_ADDRESS.CLIENT_NUMBER)
> WHERE (((dbo_ADDRXREF.ADDR_USAGE)="1"));
>
>

Access to snapshot tempfiles is denied

I take a snapshot of a report an it views fine the first time but I get the
following error on subsequent viewings:
An internal error occurred on the report server. See the error log for more
details. (rsInternalError) Get Online Help
Access to the path "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\RSTempFiles\RSFile_6b4351c3-0cfb-4c51-bda1-ac280f1d7eea" is denied.
where the name of the file changes when i re-snapshot it. I've checked the
permissions on the RSTempFiles folder and both the System and ASPNET have
Full Control over it.
Any ideas why this is still happening?
TIA,
Dan Fell.Has anyone solved this issue? I am having the same exact problem with users
who are browsers.
"Dan Fell" wrote:
> I take a snapshot of a report an it views fine the first time but I get the
> following error on subsequent viewings:
> An internal error occurred on the report server. See the error log for more
> details. (rsInternalError) Get Online Help
> Access to the path "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\RSTempFiles\RSFile_6b4351c3-0cfb-4c51-bda1-ac280f1d7eea" is denied.
> where the name of the file changes when i re-snapshot it. I've checked the
> permissions on the RSTempFiles folder and both the System and ASPNET have
> Full Control over it.
> Any ideas why this is still happening?
> TIA,
> Dan Fell.|||I am having the same exact problem too with snapshots but it doesn't
happen with all the reports. I am assuming that it's a new issue with
reporting services SP2, because snapshots always worked for me prior to
upgrading to SP2. Any help is greatly appreciated. Thanks.
T Robichaux wrote:
> Has anyone solved this issue? I am having the same exact problem with users
> who are browsers.
> "Dan Fell" wrote:
> >
> > I take a snapshot of a report an it views fine the first time but I get the
> > following error on subsequent viewings:
> >
> > An internal error occurred on the report server. See the error log for more
> > details. (rsInternalError) Get Online Help
> > Access to the path "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\RSTempFiles\RSFile_6b4351c3-0cfb-4c51-bda1-ac280f1d7eea" is denied.
> >
> > where the name of the file changes when i re-snapshot it. I've checked the
> > permissions on the RSTempFiles folder and both the System and ASPNET have
> > Full Control over it.
> >
> > Any ideas why this is still happening?
> >
> > TIA,
> >
> > Dan Fell.|||I had the same Problem: Only one of many reports with the same settings
as the others (render with temporary copy for 30 min) creates always a
file in the RSTempFiles Folder.
I checked the Readme Files and found that since SP1 (section 4.4.4 New
Configuration Settings in sp2Readme_EN.htm) there are settings for
FileSharing.
I added the settings to the configuration
...
<Add Key="WebServiceUseFileShareStorage" Value="false" />
...
<WindowsServiceUseFileShareStorage>False</WindowsServiceUseFileShareStorage>
<FileShareStorageLocation>
<Path> XXXXX </Path>
</FileShareStorageLocation>
but it still creates the temporary file. You can see that the settings
are regarded when you change the Path. Then the tempfile is placed there
and the errormessage shows the access denied for this path. Either the
setting for the useFileShare = false is ignored or there is some other
setting (in the report?) which "overwrites" the setting.
WORKAROUND:
I gave read permission to everyone on the folder
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\RSTempFiles
and it works.
System:
SQL Server 2000, Reporting Services SP2 on as w2k Server, Windows 2003
AD, Integrated Security for SQLServer and IIS, mixed language (englisch,
german) for OS and Applications.
I hope the workaround helps and I still wait for a better solution or
explanation for this file creation.
Best regards,
Markus Mahlitz
Vasu Bojja schrieb:
> I am having the same exact problem too with snapshots but it doesn't
> happen with all the reports. I am assuming that it's a new issue with
> reporting services SP2, because snapshots always worked for me prior to
> upgrading to SP2. Any help is greatly appreciated. Thanks.
>
> T Robichaux wrote:
>> Has anyone solved this issue? I am having the same exact problem with users
>> who are browsers.
>> "Dan Fell" wrote:
>> I take a snapshot of a report an it views fine the first time but I get the
>> following error on subsequent viewings:
>> An internal error occurred on the report server. See the error log for more
>> details. (rsInternalError) Get Online Help
>> Access to the path "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
>> Services\RSTempFiles\RSFile_6b4351c3-0cfb-4c51-bda1-ac280f1d7eea" is denied.
>> where the name of the file changes when i re-snapshot it. I've checked the
>> permissions on the RSTempFiles folder and both the System and ASPNET have
>> Full Control over it.
>> Any ideas why this is still happening?
>> TIA,
>> Dan Fell.
>

Access permissions

I have recently installed MSDE for the first time and created a .adp file
that connects to my MSDE server. However, when I create a new table within
the .adp file I can't add new records to it. Also, when I go to the
"Advanced" tab of the "Data Link Properties" dialog box all of the Access
Permissions are greyed out. I feel that this could be part of the problem.
Any suggestions?
Nevermind, I figured it out. I did not have the table indexed therefore I
wasn't able to add new records.
"Taz" wrote:

> I have recently installed MSDE for the first time and created a .adp file
> that connects to my MSDE server. However, when I create a new table within
> the .adp file I can't add new records to it. Also, when I go to the
> "Advanced" tab of the "Data Link Properties" dialog box all of the Access
> Permissions are greyed out. I feel that this could be part of the problem.
> Any suggestions?

access one remote SQL server using 2 different users accounts via Enterprise Manager

Hello,
I ran into a strange situation. I need to access the same remote SQL
server using 2 different user accounts at the same time. I can't seem
to do it using Enterprise Manager.
This is the first time I ran into this type of problem. I signed up for
web hosting service with a company called Netnation. They have a
strange policy- everytime, I create a database on their MS SQL server,
they give me a new user account to access the new database. so if i own
2 databases, then i will have to use 2 user names for the databases.
each user name can only access one database.
I am have problem configuring my Enterprise Manager to allow me to see
more than one database at a time because of the "one -user-one
-database" problem.
Does anyone know if there is anyway i can set up Enterprise Manager to
get round this problem?
Thank you in advance!
Eddy
One option is to register the server twice - create an alias
for the server and then register the second one with that
alias. You can use the second set of credentials to register
the second instance of the server with the alias.
So you'd end up with two server nodes pointing to the same
server but it would still be in one Enterprise Manager
console.
-Sue
On 16 Jan 2006 16:39:52 -0800, eddiekwang@.hotmail.com wrote:

>Hello,
>I ran into a strange situation. I need to access the same remote SQL
>server using 2 different user accounts at the same time. I can't seem
>to do it using Enterprise Manager.
>This is the first time I ran into this type of problem. I signed up for
>web hosting service with a company called Netnation. They have a
>strange policy- everytime, I create a database on their MS SQL server,
>they give me a new user account to access the new database. so if i own
>2 databases, then i will have to use 2 user names for the databases.
>each user name can only access one database.
>I am have problem configuring my Enterprise Manager to allow me to see
>more than one database at a time because of the "one -user-one
>-database" problem.
>Does anyone know if there is anyway i can set up Enterprise Manager to
>get round this problem?
>Thank you in advance!
>Eddy
|||Sue,
Thanks for the reply. I already tried registering the server twice but
it didn't let me. I can't remember the specifics of the error message.
Did it ever work for you?
Eddy
|||Hi Eddy,
Yes...worked fine. You need to have it registered under a
different name than the current registration. That's why I
said to create an alias first. Then register the alias. You
probably tried to register it twice with the same name.
-Sue
On 20 Jan 2006 12:34:49 -0800, eddiekwang@.hotmail.com wrote:

>Sue,
>Thanks for the reply. I already tried registering the server twice but
>it didn't let me. I can't remember the specifics of the error message.
>Did it ever work for you?
>Eddy

access one remote SQL server using 2 different users accounts via Enterprise Manager

Hello,
I ran into a strange situation. I need to access the same remote SQL
server using 2 different user accounts at the same time. I can't seem
to do it using Enterprise Manager.
This is the first time I ran into this type of problem. I signed up for
web hosting service with a company called Netnation. They have a
strange policy- everytime, I create a database on their MS SQL server,
they give me a new user account to access the new database. so if i own
2 databases, then i will have to use 2 user names for the databases.
each user name can only access one database.
I am have problem configuring my Enterprise Manager to allow me to see
more than one database at a time because of the "one -user-one
-database" problem.
Does anyone know if there is anyway i can set up Enterprise Manager to
get round this problem?
Thank you in advance!
EddyOne option is to register the server twice - create an alias
for the server and then register the second one with that
alias. You can use the second set of credentials to register
the second instance of the server with the alias.
So you'd end up with two server nodes pointing to the same
server but it would still be in one Enterprise Manager
console.
-Sue
On 16 Jan 2006 16:39:52 -0800, eddiekwang@.hotmail.com wrote:
>Hello,
>I ran into a strange situation. I need to access the same remote SQL
>server using 2 different user accounts at the same time. I can't seem
>to do it using Enterprise Manager.
>This is the first time I ran into this type of problem. I signed up for
>web hosting service with a company called Netnation. They have a
>strange policy- everytime, I create a database on their MS SQL server,
>they give me a new user account to access the new database. so if i own
>2 databases, then i will have to use 2 user names for the databases.
>each user name can only access one database.
>I am have problem configuring my Enterprise Manager to allow me to see
>more than one database at a time because of the "one -user-one
>-database" problem.
>Does anyone know if there is anyway i can set up Enterprise Manager to
>get round this problem?
>Thank you in advance!
>Eddy|||Sue,
Thanks for the reply. I already tried registering the server twice but
it didn't let me. I can't remember the specifics of the error message.
Did it ever work for you?
Eddy|||Hi Eddy,
Yes...worked fine. You need to have it registered under a
different name than the current registration. That's why I
said to create an alias first. Then register the alias. You
probably tried to register it twice with the same name.
-Sue
On 20 Jan 2006 12:34:49 -0800, eddiekwang@.hotmail.com wrote:
>Sue,
>Thanks for the reply. I already tried registering the server twice but
>it didn't let me. I can't remember the specifics of the error message.
>Did it ever work for you?
>Eddy

access one remote SQL server using 2 different users accounts via Enterprise Manager

Hello,
I ran into a strange situation. I need to access the same remote SQL
server using 2 different user accounts at the same time. I can't seem
to do it using Enterprise Manager.
This is the first time I ran into this type of problem. I signed up for
web hosting service with a company called Netnation. They have a
strange policy- everytime, I create a database on their MS SQL server,
they give me a new user account to access the new database. so if i own
2 databases, then i will have to use 2 user names for the databases.
each user name can only access one database.
I am have problem configuring my Enterprise Manager to allow me to see
more than one database at a time because of the "one -user-one
-database" problem.
Does anyone know if there is anyway i can set up Enterprise Manager to
get round this problem?
Thank you in advance!
EddyOne option is to register the server twice - create an alias
for the server and then register the second one with that
alias. You can use the second set of credentials to register
the second instance of the server with the alias.
So you'd end up with two server nodes pointing to the same
server but it would still be in one Enterprise Manager
console.
-Sue
On 16 Jan 2006 16:39:52 -0800, eddiekwang@.hotmail.com wrote:

>Hello,
>I ran into a strange situation. I need to access the same remote SQL
>server using 2 different user accounts at the same time. I can't seem
>to do it using Enterprise Manager.
>This is the first time I ran into this type of problem. I signed up for
>web hosting service with a company called Netnation. They have a
>strange policy- everytime, I create a database on their MS SQL server,
>they give me a new user account to access the new database. so if i own
>2 databases, then i will have to use 2 user names for the databases.
>each user name can only access one database.
>I am have problem configuring my Enterprise Manager to allow me to see
>more than one database at a time because of the "one -user-one
>-database" problem.
>Does anyone know if there is anyway i can set up Enterprise Manager to
>get round this problem?
>Thank you in advance!
>Eddy|||Sue,
Thanks for the reply. I already tried registering the server twice but
it didn't let me. I can't remember the specifics of the error message.
Did it ever work for you?
Eddy|||Hi Eddy,
Yes...worked fine. You need to have it registered under a
different name than the current registration. That's why I
said to create an alias first. Then register the alias. You
probably tried to register it twice with the same name.
-Sue
On 20 Jan 2006 12:34:49 -0800, eddiekwang@.hotmail.com wrote:

>Sue,
>Thanks for the reply. I already tried registering the server twice but
>it didn't let me. I can't remember the specifics of the error message.
>Did it ever work for you?
>Eddy

access is denied when starting service

We have a user who could start and stop the sql server service through EM at
one time and then he started getting an "error 5 (access is denied) occurred
while performing this service operation on the MSSQLServer service."
I removed his login and created a new one for him and gave him sysadmin
permissions but he still gets the same error when trying to start the
service.
Any ideas?
Thanks,
--
Dan D.Starting/ stopping services are OS specific. Make sure he's allowed to do
this at the OS level, also make sure that his password is correct in EM. Did
he change his password since the lat time he was able to do this?
--
TIA,
ChrisR
"Dan D." wrote:

> We have a user who could start and stop the sql server service through EM
at
> one time and then he started getting an "error 5 (access is denied) occurr
ed
> while performing this service operation on the MSSQLServer service."
> I removed his login and created a new one for him and gave him sysadmin
> permissions but he still gets the same error when trying to start the
> service.
> Any ideas?
> Thanks,
> --
> Dan D.|||I saw this recently when some of our users switched to a new Win 2003
domain.
We had forgotten to set them up in sql server.
Paul
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:7BB943F7-C48F-406A-A44D-22C200686A8C@.microsoft.com...[vbcol=seagreen]
> Starting/ stopping services are OS specific. Make sure he's allowed to do
> this at the OS level, also make sure that his password is correct in EM.
> Did
> he change his password since the lat time he was able to do this?
> --
> TIA,
> ChrisR
>
> "Dan D." wrote:
>|||They have logins in sqlserver and they are sysadmins in sqlserver.
--
Dan D.
"Paul Cahill" wrote:

> I saw this recently when some of our users switched to a new Win 2003
> domain.
> We had forgotten to set them up in sql server.
> Paul
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:7BB943F7-C48F-406A-A44D-22C200686A8C@.microsoft.com...
>
>|||I'm thinking it is OS related too. They also can't stop and start IIS. They
say they haven't changed their passwords recently.
--
Dan D.
"ChrisR" wrote:
[vbcol=seagreen]
> Starting/ stopping services are OS specific. Make sure he's allowed to do
> this at the OS level, also make sure that his password is correct in EM. D
id
> he change his password since the lat time he was able to do this?
> --
> TIA,
> ChrisR
>
> "Dan D." wrote:
>|||Another thing that happened with our new domain.
One of my colleagues (a user on our old domain) mapped a drive on the new
domain as the NEWDOMAIN\administrator (remember by password).
We then noticed that when he connected to an SQL Server on the new domain he
was logging in as NEWDOMAIN\administrator and not his OLDDOMAIN\username.
Ie the authentication of network resources affects sql server too. I guess
this makes sense but caught me out.
Maybe not the same issue for you but it looks like an authentication issue
of some sort.
Paul
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:90E92723-FA30-48F4-9415-50A4A0EF8797@.microsoft.com...[vbcol=seagreen]
> I'm thinking it is OS related too. They also can't stop and start IIS.
> They
> say they haven't changed their passwords recently.
> --
> Dan D.
>
> "ChrisR" wrote:
>

access is denied when starting service

We have a user who could start and stop the sql server service through EM at
one time and then he started getting an "error 5 (access is denied) occurred
while performing this service operation on the MSSQLServer service."
I removed his login and created a new one for him and gave him sysadmin
permissions but he still gets the same error when trying to start the
service.
Any ideas?
Thanks,
Dan D.
Starting/ stopping services are OS specific. Make sure he's allowed to do
this at the OS level, also make sure that his password is correct in EM. Did
he change his password since the lat time he was able to do this?
TIA,
ChrisR
"Dan D." wrote:

> We have a user who could start and stop the sql server service through EM at
> one time and then he started getting an "error 5 (access is denied) occurred
> while performing this service operation on the MSSQLServer service."
> I removed his login and created a new one for him and gave him sysadmin
> permissions but he still gets the same error when trying to start the
> service.
> Any ideas?
> Thanks,
> --
> Dan D.
|||I saw this recently when some of our users switched to a new Win 2003
domain.
We had forgotten to set them up in sql server.
Paul
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:7BB943F7-C48F-406A-A44D-22C200686A8C@.microsoft.com...[vbcol=seagreen]
> Starting/ stopping services are OS specific. Make sure he's allowed to do
> this at the OS level, also make sure that his password is correct in EM.
> Did
> he change his password since the lat time he was able to do this?
> --
> TIA,
> ChrisR
>
> "Dan D." wrote:
|||They have logins in sqlserver and they are sysadmins in sqlserver.
Dan D.
"Paul Cahill" wrote:

> I saw this recently when some of our users switched to a new Win 2003
> domain.
> We had forgotten to set them up in sql server.
> Paul
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:7BB943F7-C48F-406A-A44D-22C200686A8C@.microsoft.com...
>
>
|||I'm thinking it is OS related too. They also can't stop and start IIS. They
say they haven't changed their passwords recently.
Dan D.
"ChrisR" wrote:
[vbcol=seagreen]
> Starting/ stopping services are OS specific. Make sure he's allowed to do
> this at the OS level, also make sure that his password is correct in EM. Did
> he change his password since the lat time he was able to do this?
> --
> TIA,
> ChrisR
>
> "Dan D." wrote:
|||Another thing that happened with our new domain.
One of my colleagues (a user on our old domain) mapped a drive on the new
domain as the NEWDOMAIN\administrator (remember by password).
We then noticed that when he connected to an SQL Server on the new domain he
was logging in as NEWDOMAIN\administrator and not his OLDDOMAIN\username.
Ie the authentication of network resources affects sql server too. I guess
this makes sense but caught me out.
Maybe not the same issue for you but it looks like an authentication issue
of some sort.
Paul
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:90E92723-FA30-48F4-9415-50A4A0EF8797@.microsoft.com...[vbcol=seagreen]
> I'm thinking it is OS related too. They also can't stop and start IIS.
> They
> say they haven't changed their passwords recently.
> --
> Dan D.
>
> "ChrisR" wrote:

access is denied when starting service

We have a user who could start and stop the sql server service through EM at
one time and then he started getting an "error 5 (access is denied) occurred
while performing this service operation on the MSSQLServer service."
I removed his login and created a new one for him and gave him sysadmin
permissions but he still gets the same error when trying to start the
service.
Any ideas?
Thanks,
--
Dan D.Starting/ stopping services are OS specific. Make sure he's allowed to do
this at the OS level, also make sure that his password is correct in EM. Did
he change his password since the lat time he was able to do this?
--
TIA,
ChrisR
"Dan D." wrote:
> We have a user who could start and stop the sql server service through EM at
> one time and then he started getting an "error 5 (access is denied) occurred
> while performing this service operation on the MSSQLServer service."
> I removed his login and created a new one for him and gave him sysadmin
> permissions but he still gets the same error when trying to start the
> service.
> Any ideas?
> Thanks,
> --
> Dan D.|||I saw this recently when some of our users switched to a new Win 2003
domain.
We had forgotten to set them up in sql server.
Paul
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:7BB943F7-C48F-406A-A44D-22C200686A8C@.microsoft.com...
> Starting/ stopping services are OS specific. Make sure he's allowed to do
> this at the OS level, also make sure that his password is correct in EM.
> Did
> he change his password since the lat time he was able to do this?
> --
> TIA,
> ChrisR
>
> "Dan D." wrote:
>> We have a user who could start and stop the sql server service through EM
>> at
>> one time and then he started getting an "error 5 (access is denied)
>> occurred
>> while performing this service operation on the MSSQLServer service."
>> I removed his login and created a new one for him and gave him sysadmin
>> permissions but he still gets the same error when trying to start the
>> service.
>> Any ideas?
>> Thanks,
>> --
>> Dan D.|||They have logins in sqlserver and they are sysadmins in sqlserver.
--
Dan D.
"Paul Cahill" wrote:
> I saw this recently when some of our users switched to a new Win 2003
> domain.
> We had forgotten to set them up in sql server.
> Paul
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:7BB943F7-C48F-406A-A44D-22C200686A8C@.microsoft.com...
> > Starting/ stopping services are OS specific. Make sure he's allowed to do
> > this at the OS level, also make sure that his password is correct in EM.
> > Did
> > he change his password since the lat time he was able to do this?
> > --
> > TIA,
> > ChrisR
> >
> >
> > "Dan D." wrote:
> >
> >> We have a user who could start and stop the sql server service through EM
> >> at
> >> one time and then he started getting an "error 5 (access is denied)
> >> occurred
> >> while performing this service operation on the MSSQLServer service."
> >>
> >> I removed his login and created a new one for him and gave him sysadmin
> >> permissions but he still gets the same error when trying to start the
> >> service.
> >>
> >> Any ideas?
> >>
> >> Thanks,
> >> --
> >> Dan D.
>
>|||I'm thinking it is OS related too. They also can't stop and start IIS. They
say they haven't changed their passwords recently.
--
Dan D.
"ChrisR" wrote:
> Starting/ stopping services are OS specific. Make sure he's allowed to do
> this at the OS level, also make sure that his password is correct in EM. Did
> he change his password since the lat time he was able to do this?
> --
> TIA,
> ChrisR
>
> "Dan D." wrote:
> > We have a user who could start and stop the sql server service through EM at
> > one time and then he started getting an "error 5 (access is denied) occurred
> > while performing this service operation on the MSSQLServer service."
> >
> > I removed his login and created a new one for him and gave him sysadmin
> > permissions but he still gets the same error when trying to start the
> > service.
> >
> > Any ideas?
> >
> > Thanks,
> > --
> > Dan D.|||Another thing that happened with our new domain.
One of my colleagues (a user on our old domain) mapped a drive on the new
domain as the NEWDOMAIN\administrator (remember by password).
We then noticed that when he connected to an SQL Server on the new domain he
was logging in as NEWDOMAIN\administrator and not his OLDDOMAIN\username.
Ie the authentication of network resources affects sql server too. I guess
this makes sense but caught me out.
Maybe not the same issue for you but it looks like an authentication issue
of some sort.
Paul
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:90E92723-FA30-48F4-9415-50A4A0EF8797@.microsoft.com...
> I'm thinking it is OS related too. They also can't stop and start IIS.
> They
> say they haven't changed their passwords recently.
> --
> Dan D.
>
> "ChrisR" wrote:
>> Starting/ stopping services are OS specific. Make sure he's allowed to do
>> this at the OS level, also make sure that his password is correct in EM.
>> Did
>> he change his password since the lat time he was able to do this?
>> --
>> TIA,
>> ChrisR
>>
>> "Dan D." wrote:
>> > We have a user who could start and stop the sql server service through
>> > EM at
>> > one time and then he started getting an "error 5 (access is denied)
>> > occurred
>> > while performing this service operation on the MSSQLServer service."
>> >
>> > I removed his login and created a new one for him and gave him sysadmin
>> > permissions but he still gets the same error when trying to start the
>> > service.
>> >
>> > Any ideas?
>> >
>> > Thanks,
>> > --
>> > Dan D.

Access denied thru ole db but not DSN

I have an asp page that at one time worked.
Now I get the below error msg. It only happens on my asp
page. I can run through access or vb and it connects fine.
It also works fine through a dsn. I have looked everywhere
to no avail. it is sql 2k sp 3.
any help is mucho appreciated
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNMPNTW]Access denied.
/macro/conn_test.asp, line 20
code:
<%
dim lca, sql, region
set lca = server.createobject
("adodb.connection")
lca.connectionstring
= " Provider=SQLoLEDB;Server=231v9;UID=LCAP;
Pwd=fxtool;Datab
ase=LCAOPS"
lca.connectiontimeout = 30
lca.Open
lca.close
%>Have you checked permissions on the registry keys? You could use regmon
from sysinternals.com to see what keys it accesses and see if it gets any
access denied messages.
Or maybe it can't find (or doesn't have security to see) the actual OLE DB
driver?
Will a UDL on the IIS machine, using that same driver and connection info,
connect to the SQL Server?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||This connection that is failing is using named pipes. Try adding this to
the connect string,
Network Library = dbmssocn. this will force it to use tcp/ip.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I am having the same problem Kevin is.
when I try to connect to my SQL Server (it is on a separate box from my
development workstation) through a simple windows application it works fine.
When I use the exact same code in an .aspx file, I get SQL Server does not
exist or access is denied.
I tried adding Network Library=dbmssocn, but when I did, even the windows
app stopped working.
Any suggetions would be appretiated.
thank you
Kirk Graves
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:fQ93b$d5DHA.2768@.cpmsftngxa07.phx.gbl...
> This connection that is failing is using named pipes. Try adding this to
> the connect string,
> Network Library = dbmssocn. this will force it to use tcp/ip.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Access denied on deployment

Just deployed my web app at an ISP. The very first time I hit my database, I get an exception

'SQL Server does not exist or access denied'

I'm trying to connect using an SQL account with user id and password (which work fine if I go in using SQL Query Analyser).

The connection string contains the initial catalog, user id and password.

Can anybody help please?

Thanks,
BernieI assume you checked the remote data source address too.

Did you try connecting to the remote database using the Server Explorer inside of VS.Net? If you can connect to it through there, then just drag a table from the remote data source onto some test page. Look in the VS.Net generated code in the code behind and find where it creates the connection. Copy and paste this connection string info to where you need it.|||Are you sure you're using the correct server in the connection string?

When I first started doing this, in SQL Client Network Utitlity, I gave my server an Alias - - locally, that's what I was using, and when I uploaded the pages to the server, I'd forget to change the alias, and of course, it wouldn't work, since the host has no idea what that alias is.|||Thanks for the replies guys. I did as McMurdoStation suggested.
I could 'see' the server database and all of its tables from the VS.Net SQL server , so proceeded to create an SQL Adaptor/SQL Connection datagrid etc... on a test page.

When it tried to connect, the login failed, so I assume this is the same problem.

The connection string looks like:-
value="initial catalog=ClubNoticeBoard; user id=ClubNoticeBoard;pwd=*****;packet size=4096"

The one in the test page I created looks like
this.sqlConnection1.ConnectionString = "workstation id=BERNIE;packet size=4096;user id=ClubNoticeBoard;data source=\"URL here\";persist security info=False;initial catalog=ClubNoticeBoard";

I've just read somewhere that the ASPNET account must have access, that sounds reasobale as I can access the database fine except through the aspx pages. How do I go about setting up an ASPNET account using the SQL Server Enterprise Manager.

Cheers,
Bernie|||under SQL Server explorer, expand on the name of your database, right click on 'Users' and click on 'Add New Database user'. then add ASPNET user.

HTH.|||Ok, bearing in mind that this is a shared server at an ISP, when i did what you suggested, I get the New User box up requesting a Login name and User name. The Login name is a drop down list of other logins that already exist, plus BUILTINS\Administrators.

What do I select in that login box, I can type in ASPNET but it then gives me an error saying 'login doesn't exist'.

Assuming I can eventually get a Login of ASPNET what would I put in the User name.

Cheers,
Bernie|||I think the database user is a red herring, as my local development machine does not have an ASPNET user.

Although there is an ASPNET account for the machine itself, and so I suspect that this is what I need. I have asked the ISP to set one up.

Cheers,
bernie|||you have to select <new> from the list and then it will let you add a new user.|||I tried that but it said I had to be an adminsitrator to complete the task.

Can somebody tell me what exactly needs to be setup on the box?

1. Is it a new user account i.e machinename/ASPNET?
2. Is it a new SQL Server log with name ASPNET?

If no. 1, what happens about the password, how can asp.net use it if doesn't know it?
If no. 2, would it use WIndows Authentication, or an SQL account?

I must say this all seems pretty standard stuff, I can't understand why nobody has come up with a definitive answer. Maybe the ISP i'm using don't know their ASP.NET stuff and they havn't set things up correctly.

Cheers,
Bernie|||when you install vs.net it creates a ASP.NET user account on your system. this is for internet users to login to your comp. but for the aspx pages to access your databases, you still need to add the machinename\ASPNET account inside the SQL Server.

when you select <new> user from the list, another window will come up to fill in the details for the new user.

there would be a button with "..." on it which will bring up all the user accounts on that computer. click on it and you can choose the ASPNET account from the list.
and you can use windows authentication. and dont forget to choose the database.

we usually need not set a password. you can use integrated security in your connection string. something like this :

"server=local;database=Northwind;Integrated Security=SSPI "

and i guess you need to be an admin to create a user account in SQL Server.

HTH|||Thanks, the problem was I couldn't see ASPNET in the drop down list.

All sorted now and working (up to a point), I've some other permission issues to sort out.

Thanks everybody for your help.

Bernie