I've got a SQL database and I need to see what the data it looks like

Is there a program like Access that can open a SQL database and display the information?

You can link the tables and view data using Access.

You can also install the SQL Client tools and use Enterprise Manager or Management Studio (depending on version).

|||Hi,

If you have Access in place which supports ADP projects you can establish a connection to the database and will be able to view almost all user defined objects in the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

IUSR Web Access to DB - Separate Servers

I have NT SQL 7 and Web Server on same box. I have a new server with W2K and SQL 2000 installed (no IIS). I am migrating the databases over and when done I will install W2K on the old box and it will remain the IIS Web server front end to the new SQL 2000 Server.

On the old configuration, IIS and SQL 7 on the same box, I added the IUSER to the database from the local computer users list when needed for web access. How do I add that user now that the boxes are seperate? The acccout is not a local account on the new machine and the account is a loca account on the other machine so it does not authenticate to the domain so I can't pull it in from there.

Thanks for any help.

--Steve

ssantos@.csuchico.eduSimple answer is to use SQL Authentication.

Create a SQL user on your SQL box, then use a non-SSPI connection in your ASP/ASPX.

More complicated, but ultimately better answer:
Create a new domain user, give it the same permissions on the IIS machine as the IUSER, restrict it's permissions elsewhere on the domain, then add them as a database user in the SQL box.

Its really a strange World over in....

...the Access forum...so many bad practices
Like this one
http://www.dbforums.com/showthread.php?p=6284279&posted=1#post6284279I have had my fill of discouragement today, so I am not following the thread but what do you expect from people who work with PlaySkool's "My First Database Engine". Admittedly, it's been a couple of versions of Office since I have dealt with a lot of that crap but I can not imagine it has gotten a lot better nor the average Access practitioner has become that much more of a sophisticated programmer.|||the average Access practitioner has become that much more of a sophisticated programmer.

pfffffffffffftttt...sophisticated?

Scrub, more likely|||...the Access forum...so many bad practices

Like this one

http://www.dbforums.com/showthread.php?p=6284279&posted=1#post6284279
Could be worse. Could be Excel. Remember the spat I got into the with Excel developer who insisted that spreadsheets were equal to or superior to databases?|||No. I think I missed that one.|||After reading that thread I think I am going to go sit in the corner and cry.|||I know how much you guys bash Access - but that's how I started!
I still enjoy using it for small scale stuff.

I would not be the developer I am today without Access.|||I think Access is a great product. The problems occur when it is used beyond its capacity.|||Yeah, hell, I did a boat load of Access...it's not Access

It's like giving a loaded gun to a monkey however

And the Excel thing, I'm in the middle of convert a "system" to .net and SQL

One of the things they like to do, is cell reference, where that's more difficult to reference rows to each other

However, what I find is that a lot of it can be handled with math, which the excel "programmers" never take the time to investigate

Ask an excel "programmer" to do a set based running total

fuuuuget about it|||Should this not be in chat?|||It's a really strange world when the Sybas^H^H^H^H^HSQL Server guys are busting on Access guys...

I happen to like SQL Server. Most of my work I've done on SQL Server. But let's be honest: better adherence to the SQL standard just means it's more standard, not that it's more relationally correct. And given that most client apps written for SQL Server are done in Visual Basic, you can hardly get all uppity about your choice of imperative languages.

BTW, Kaiser, why are you criticizing them for utilizing a progress bar? Every human interface guideline I've ever seen, from the original Macintosh HIG manuals to the Windows guidelines, states that if a long task is running that the system should display a progress bar. Where's the bad practice here?|||Oh, my dear sco08y

What makes you think a batch process should have the user sit there and watch?

It is a culture of bs m$ crap, that's what

Any process that is not in the milliseconds should be a background asynchronous task

better adherence to the SQL standard just means it's more standard, not that it's more relationally correct.

"Any Response to that"

"Yeah: WHAT?"|||Any process that is not in the milliseconds should be a background asynchronous task

Yeah, that's what the minimize button is for. But I see what you're getting at, and it's really due to deficiencies in the OS.

Besides, even the so-called server operating systems like Unix require a lot of babysitting of background tasks. And I've never seen a good UI for sifting through log files.

Issues with Fully Qualified Table Access

I recently migrated Database1, which contains Table1, from one SQL Server
2000 box to another (Source and Dest, I'll call them). This is probably the
50th database migration I've performed within the past few months. My
general steps for migration are:
1. Create and execute the sp_help_revlogin stored procedure on Source to
copy all the SQL Server logins from Source to Dest.
2. Copy the sp_help_revlogin output and execute on Dest server.
3. Backup Database1 on Source, and restore Database1 on Dest.
4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
orphaned logins on Dest's Database1.
With Database1, there are many database objects, tables, created using the
login "User1." Here's where I'm stumped.
1. When I try to select from the User1-created Table1 on *SOURCE*, while I'm
logged in as User1, using the following stmt:
SELECT * FROM TABLE1
... I get my resultset, no issues.
2. After the migration, when I try to select from the User1-created Table1
on *DEST*, while I'm logged in as User1, using the following stmt:
SELECT * FROM TABLE1
... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line 1.
Invalid object name 'TABLE1'" and *no resultset*.
At least I'm relieved then when I try fully qualifying the table
reference:
SELECT * FROM USER1.TABLE1
... I get my resultset back with no error messages.
3. Next, I try assigning User1 to the System Administrators server role in
Database1 on *DEST* to try to find a way around the issue above. No dice--
same error message. Also, viewing the Users choice under Database1 on
*DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
"sp_change_users_login 'Update_One', 'User1', 'User1'" returns the message,
"The number of orphaned users fixed by updating users was 0." Also, trying
to drop User1 via SQLEM -> Security -> Logins -> Database Access tab, from
Database1, gives me "Error 15183: The user owns objects in the database and
cannot be dropped." (I was experimenting to see if I can drop and re-add
user.)
The problem is-- application dependencies exist where the application
connects to the database server as User1, and appears to *not* fully qualify
table access. This is a legacy application that's existed without
modification for years and I'm afraid can't be modified to have qualified
table access.
However, I'm stumped by the issue. I'm wondering what variables and
configuration settings I haven't considered yet that, on SOURCE, would allow
me to *not* qualify table access and be OK with it, yet on DEST, seem to
require table qualification.
Any help and assistance would be greatly appreciated in advance.
Thanks,
- H-H Lee,
First, true confession: I did not read the whole thing and attempt to
understand it all. However...
Your process of backup and restore has a common flaw. This can result in
the restored database having a different owner of the database from the
internal user mapped as dbo. If this is your case, then you should do the
following:
sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
If this works, then great.
RLF
"H Lee" <anon@.anon.com> wrote in message
news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>I recently migrated Database1, which contains Table1, from one SQL Server
> 2000 box to another (Source and Dest, I'll call them). This is probably
> the
> 50th database migration I've performed within the past few months. My
> general steps for migration are:
> 1. Create and execute the sp_help_revlogin stored procedure on Source to
> copy all the SQL Server logins from Source to Dest.
> 2. Copy the sp_help_revlogin output and execute on Dest server.
> 3. Backup Database1 on Source, and restore Database1 on Dest.
> 4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
> orphaned logins on Dest's Database1.
> With Database1, there are many database objects, tables, created using the
> login "User1." Here's where I'm stumped.
> 1. When I try to select from the User1-created Table1 on *SOURCE*, while
> I'm
> logged in as User1, using the following stmt:
> SELECT * FROM TABLE1
> ... I get my resultset, no issues.
> 2. After the migration, when I try to select from the User1-created Table1
> on *DEST*, while I'm logged in as User1, using the following stmt:
> SELECT * FROM TABLE1
> ... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line 1.
> Invalid object name 'TABLE1'" and *no resultset*.
> At least I'm relieved then when I try fully qualifying the table
> reference:
> SELECT * FROM USER1.TABLE1
> ... I get my resultset back with no error messages.
> 3. Next, I try assigning User1 to the System Administrators server role in
> Database1 on *DEST* to try to find a way around the issue above. No
> dice--
> same error message. Also, viewing the Users choice under Database1 on
> *DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
> Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
> "sp_change_users_login 'Update_One', 'User1', 'User1'" returns the
> message,
> "The number of orphaned users fixed by updating users was 0." Also,
> trying
> to drop User1 via SQLEM -> Security -> Logins -> Database Access tab, from
> Database1, gives me "Error 15183: The user owns objects in the database
> and
> cannot be dropped." (I was experimenting to see if I can drop and re-add
> user.)
> The problem is-- application dependencies exist where the application
> connects to the database server as User1, and appears to *not* fully
> qualify
> table access. This is a legacy application that's existed without
> modification for years and I'm afraid can't be modified to have qualified
> table access.
> However, I'm stumped by the issue. I'm wondering what variables and
> configuration settings I haven't considered yet that, on SOURCE, would
> allow
> me to *not* qualify table access and be OK with it, yet on DEST, seem to
> require table qualification.
> Any help and assistance would be greatly appreciated in advance.
> Thanks,
> - H-
>
>
>|||Russell,
I appreciate your effort and reply. I was wondering if you could explain
how I might eliminate the initial backup and restore flaw.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OSx4o0p%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>H Lee,
> First, true confession: I did not read the whole thing and attempt to
> understand it all. However...
> Your process of backup and restore has a common flaw. This can result in
> the restored database having a different owner of the database from the
> internal user mapped as dbo. If this is your case, then you should do the
> following:
> sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
> sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
> If this works, then great.
> RLF
>
> "H Lee" <anon@.anon.com> wrote in message
> news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>|||If what I described is actually the problem (and I do not know that it is)
then there is little that you can do about it. By design, SQL Logins are
independent between servers. If the owner of the databases is a domain
user, then the SID should be the same, but I have not played with it to
determine what other issues arise.
1 - If the database to which you restore was created with the wrong owner,
you may need to do the reset steps only once. Then, once set db is owned by
the proper account, there will be no unsynchronized dbo user inside the
database.
2 - If that does not work, include the resetting of the owner during the
restore process (script restore followed by the code to fiddle with the
owner).
3 - If the owner of the database on both servers is 'sa' then that might be
sufficient to avoid the problem since the 'sa' account always has the same
id. (But your application, like some I have seen, may demand that the owner
of the database be an application specific owner.)
It is not really a *** problem ***, just a consequence of two independent
machines.
RLF
"H Lee" <anon@.anon.com> wrote in message
news:OXb5bSr%23FHA.2464@.TK2MSFTNGP15.phx.gbl...
> Russell,
> I appreciate your effort and reply. I was wondering if you could explain
> how I might eliminate the initial backup and restore flaw.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OSx4o0p%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>

Issues with Fully Qualified Table Access

I recently migrated Database1, which contains Table1, from one SQL Server
2000 box to another (Source and Dest, I'll call them). This is probably the
50th database migration I've performed within the past few months. My
general steps for migration are:
1. Create and execute the sp_help_revlogin stored procedure on Source to
copy all the SQL Server logins from Source to Dest.
2. Copy the sp_help_revlogin output and execute on Dest server.
3. Backup Database1 on Source, and restore Database1 on Dest.
4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
orphaned logins on Dest's Database1.
With Database1, there are many database objects, tables, created using the
login "User1." Here's where I'm stumped.
1. When I try to select from the User1-created Table1 on *SOURCE*, while I'm
logged in as User1, using the following stmt:
SELECT * FROM TABLE1
... I get my resultset, no issues.
2. After the migration, when I try to select from the User1-created Table1
on *DEST*, while I'm logged in as User1, using the following stmt:
SELECT * FROM TABLE1
... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line 1.
Invalid object name 'TABLE1'" and *no resultset*.
At least I'm relieved then when I try fully qualifying the table
reference:
SELECT * FROM USER1.TABLE1
.... I get my resultset back with no error messages.
3. Next, I try assigning User1 to the System Administrators server role in
Database1 on *DEST* to try to find a way around the issue above. No dice--
same error message. Also, viewing the Users choice under Database1 on
*DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
"sp_change_users_login 'Update_One', 'User1', 'User1'" returns the message,
"The number of orphaned users fixed by updating users was 0." Also, trying
to drop User1 via SQLEM -> Security -> Logins -> Database Access tab, from
Database1, gives me "Error 15183: The user owns objects in the database and
cannot be dropped." (I was experimenting to see if I can drop and re-add
user.)
The problem is-- application dependencies exist where the application
connects to the database server as User1, and appears to *not* fully qualify
table access. This is a legacy application that's existed without
modification for years and I'm afraid can't be modified to have qualified
table access.
However, I'm stumped by the issue. I'm wondering what variables and
configuration settings I haven't considered yet that, on SOURCE, would allow
me to *not* qualify table access and be OK with it, yet on DEST, seem to
require table qualification.
Any help and assistance would be greatly appreciated in advance.
Thanks,
- H-
H Lee,
First, true confession: I did not read the whole thing and attempt to
understand it all. However...
Your process of backup and restore has a common flaw. This can result in
the restored database having a different owner of the database from the
internal user mapped as dbo. If this is your case, then you should do the
following:
sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
If this works, then great.
RLF
"H Lee" <anon@.anon.com> wrote in message
news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>I recently migrated Database1, which contains Table1, from one SQL Server
> 2000 box to another (Source and Dest, I'll call them). This is probably
> the
> 50th database migration I've performed within the past few months. My
> general steps for migration are:
> 1. Create and execute the sp_help_revlogin stored procedure on Source to
> copy all the SQL Server logins from Source to Dest.
> 2. Copy the sp_help_revlogin output and execute on Dest server.
> 3. Backup Database1 on Source, and restore Database1 on Dest.
> 4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
> orphaned logins on Dest's Database1.
> With Database1, there are many database objects, tables, created using the
> login "User1." Here's where I'm stumped.
> 1. When I try to select from the User1-created Table1 on *SOURCE*, while
> I'm
> logged in as User1, using the following stmt:
> SELECT * FROM TABLE1
> ... I get my resultset, no issues.
> 2. After the migration, when I try to select from the User1-created Table1
> on *DEST*, while I'm logged in as User1, using the following stmt:
> SELECT * FROM TABLE1
> ... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line 1.
> Invalid object name 'TABLE1'" and *no resultset*.
> At least I'm relieved then when I try fully qualifying the table
> reference:
> SELECT * FROM USER1.TABLE1
> ... I get my resultset back with no error messages.
> 3. Next, I try assigning User1 to the System Administrators server role in
> Database1 on *DEST* to try to find a way around the issue above. No
> dice--
> same error message. Also, viewing the Users choice under Database1 on
> *DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
> Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
> "sp_change_users_login 'Update_One', 'User1', 'User1'" returns the
> message,
> "The number of orphaned users fixed by updating users was 0." Also,
> trying
> to drop User1 via SQLEM -> Security -> Logins -> Database Access tab, from
> Database1, gives me "Error 15183: The user owns objects in the database
> and
> cannot be dropped." (I was experimenting to see if I can drop and re-add
> user.)
> The problem is-- application dependencies exist where the application
> connects to the database server as User1, and appears to *not* fully
> qualify
> table access. This is a legacy application that's existed without
> modification for years and I'm afraid can't be modified to have qualified
> table access.
> However, I'm stumped by the issue. I'm wondering what variables and
> configuration settings I haven't considered yet that, on SOURCE, would
> allow
> me to *not* qualify table access and be OK with it, yet on DEST, seem to
> require table qualification.
> Any help and assistance would be greatly appreciated in advance.
> Thanks,
> - H-
>
>
>
|||Russell,
I appreciate your effort and reply. I was wondering if you could explain
how I might eliminate the initial backup and restore flaw.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OSx4o0p%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>H Lee,
> First, true confession: I did not read the whole thing and attempt to
> understand it all. However...
> Your process of backup and restore has a common flaw. This can result in
> the restored database having a different owner of the database from the
> internal user mapped as dbo. If this is your case, then you should do the
> following:
> sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
> sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
> If this works, then great.
> RLF
>
> "H Lee" <anon@.anon.com> wrote in message
> news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>
|||If what I described is actually the problem (and I do not know that it is)
then there is little that you can do about it. By design, SQL Logins are
independent between servers. If the owner of the databases is a domain
user, then the SID should be the same, but I have not played with it to
determine what other issues arise.
1 - If the database to which you restore was created with the wrong owner,
you may need to do the reset steps only once. Then, once set db is owned by
the proper account, there will be no unsynchronized dbo user inside the
database.
2 - If that does not work, include the resetting of the owner during the
restore process (script restore followed by the code to fiddle with the
owner).
3 - If the owner of the database on both servers is 'sa' then that might be
sufficient to avoid the problem since the 'sa' account always has the same
id. (But your application, like some I have seen, may demand that the owner
of the database be an application specific owner.)
It is not really a *** problem ***, just a consequence of two independent
machines.
RLF
"H Lee" <anon@.anon.com> wrote in message
news:OXb5bSr%23FHA.2464@.TK2MSFTNGP15.phx.gbl...
> Russell,
> I appreciate your effort and reply. I was wondering if you could explain
> how I might eliminate the initial backup and restore flaw.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OSx4o0p%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>
sql

Issues with Fully Qualified Table Access

I recently migrated Database1, which contains Table1, from one SQL Server
2000 box to another (Source and Dest, I'll call them). This is probably the
50th database migration I've performed within the past few months. My
general steps for migration are:
1. Create and execute the sp_help_revlogin stored procedure on Source to
copy all the SQL Server logins from Source to Dest.
2. Copy the sp_help_revlogin output and execute on Dest server.
3. Backup Database1 on Source, and restore Database1 on Dest.
4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
orphaned logins on Dest's Database1.
With Database1, there are many database objects, tables, created using the
login "User1." Here's where I'm stumped.
1. When I try to select from the User1-created Table1 on *SOURCE*, while I'm
logged in as User1, using the following stmt:
SELECT * FROM TABLE1
... I get my resultset, no issues.
2. After the migration, when I try to select from the User1-created Table1
on *DEST*, while I'm logged in as User1, using the following stmt:
SELECT * FROM TABLE1
... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line 1.
Invalid object name 'TABLE1'" and *no resultset*.
At least I'm relieved then when I try fully qualifying the table
reference:
SELECT * FROM USER1.TABLE1
... I get my resultset back with no error messages.
3. Next, I try assigning User1 to the System Administrators server role in
Database1 on *DEST* to try to find a way around the issue above. No dice--
same error message. Also, viewing the Users choice under Database1 on
*DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
"sp_change_users_login 'Update_One', 'User1', 'User1'" returns the message,
"The number of orphaned users fixed by updating users was 0." Also, trying
to drop User1 via SQLEM -> Security -> Logins -> Database Access tab, from
Database1, gives me "Error 15183: The user owns objects in the database and
cannot be dropped." (I was experimenting to see if I can drop and re-add
user.)
The problem is-- application dependencies exist where the application
connects to the database server as User1, and appears to *not* fully qualify
table access. This is a legacy application that's existed without
modification for years and I'm afraid can't be modified to have qualified
table access.
However, I'm stumped by the issue. I'm wondering what variables and
configuration settings I haven't considered yet that, on SOURCE, would allow
me to *not* qualify table access and be OK with it, yet on DEST, seem to
require table qualification.
Any help and assistance would be greatly appreciated in advance.
Thanks,
- H-H Lee,
First, true confession: I did not read the whole thing and attempt to
understand it all. However...
Your process of backup and restore has a common flaw. This can result in
the restored database having a different owner of the database from the
internal user mapped as dbo. If this is your case, then you should do the
following:
sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
If this works, then great.
RLF
"H Lee" <anon@.anon.com> wrote in message
news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>I recently migrated Database1, which contains Table1, from one SQL Server
> 2000 box to another (Source and Dest, I'll call them). This is probably
> the
> 50th database migration I've performed within the past few months. My
> general steps for migration are:
> 1. Create and execute the sp_help_revlogin stored procedure on Source to
> copy all the SQL Server logins from Source to Dest.
> 2. Copy the sp_help_revlogin output and execute on Dest server.
> 3. Backup Database1 on Source, and restore Database1 on Dest.
> 4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
> orphaned logins on Dest's Database1.
> With Database1, there are many database objects, tables, created using the
> login "User1." Here's where I'm stumped.
> 1. When I try to select from the User1-created Table1 on *SOURCE*, while
> I'm
> logged in as User1, using the following stmt:
> SELECT * FROM TABLE1
> ... I get my resultset, no issues.
> 2. After the migration, when I try to select from the User1-created Table1
> on *DEST*, while I'm logged in as User1, using the following stmt:
> SELECT * FROM TABLE1
> ... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line 1.
> Invalid object name 'TABLE1'" and *no resultset*.
> At least I'm relieved then when I try fully qualifying the table
> reference:
> SELECT * FROM USER1.TABLE1
> ... I get my resultset back with no error messages.
> 3. Next, I try assigning User1 to the System Administrators server role in
> Database1 on *DEST* to try to find a way around the issue above. No
> dice--
> same error message. Also, viewing the Users choice under Database1 on
> *DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
> Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
> "sp_change_users_login 'Update_One', 'User1', 'User1'" returns the
> message,
> "The number of orphaned users fixed by updating users was 0." Also,
> trying
> to drop User1 via SQLEM -> Security -> Logins -> Database Access tab, from
> Database1, gives me "Error 15183: The user owns objects in the database
> and
> cannot be dropped." (I was experimenting to see if I can drop and re-add
> user.)
> The problem is-- application dependencies exist where the application
> connects to the database server as User1, and appears to *not* fully
> qualify
> table access. This is a legacy application that's existed without
> modification for years and I'm afraid can't be modified to have qualified
> table access.
> However, I'm stumped by the issue. I'm wondering what variables and
> configuration settings I haven't considered yet that, on SOURCE, would
> allow
> me to *not* qualify table access and be OK with it, yet on DEST, seem to
> require table qualification.
> Any help and assistance would be greatly appreciated in advance.
> Thanks,
> - H-
>
>
>|||Russell,
I appreciate your effort and reply. I was wondering if you could explain
how I might eliminate the initial backup and restore flaw.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OSx4o0p%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>H Lee,
> First, true confession: I did not read the whole thing and attempt to
> understand it all. However...
> Your process of backup and restore has a common flaw. This can result in
> the restored database having a different owner of the database from the
> internal user mapped as dbo. If this is your case, then you should do the
> following:
> sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
> sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
> If this works, then great.
> RLF
>
> "H Lee" <anon@.anon.com> wrote in message
> news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>>I recently migrated Database1, which contains Table1, from one SQL Server
>> 2000 box to another (Source and Dest, I'll call them). This is probably
>> the
>> 50th database migration I've performed within the past few months. My
>> general steps for migration are:
>> 1. Create and execute the sp_help_revlogin stored procedure on Source to
>> copy all the SQL Server logins from Source to Dest.
>> 2. Copy the sp_help_revlogin output and execute on Dest server.
>> 3. Backup Database1 on Source, and restore Database1 on Dest.
>> 4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
>> orphaned logins on Dest's Database1.
>> With Database1, there are many database objects, tables, created using
>> the
>> login "User1." Here's where I'm stumped.
>> 1. When I try to select from the User1-created Table1 on *SOURCE*, while
>> I'm
>> logged in as User1, using the following stmt:
>> SELECT * FROM TABLE1
>> ... I get my resultset, no issues.
>> 2. After the migration, when I try to select from the User1-created
>> Table1
>> on *DEST*, while I'm logged in as User1, using the following stmt:
>> SELECT * FROM TABLE1
>> ... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line
>> 1.
>> Invalid object name 'TABLE1'" and *no resultset*.
>> At least I'm relieved then when I try fully qualifying the table
>> reference:
>> SELECT * FROM USER1.TABLE1
>> ... I get my resultset back with no error messages.
>> 3. Next, I try assigning User1 to the System Administrators server role
>> in
>> Database1 on *DEST* to try to find a way around the issue above. No
>> dice--
>> same error message. Also, viewing the Users choice under Database1 on
>> *DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
>> Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
>> "sp_change_users_login 'Update_One', 'User1', 'User1'" returns the
>> message,
>> "The number of orphaned users fixed by updating users was 0." Also,
>> trying
>> to drop User1 via SQLEM -> Security -> Logins -> Database Access tab,
>> from
>> Database1, gives me "Error 15183: The user owns objects in the database
>> and
>> cannot be dropped." (I was experimenting to see if I can drop and re-add
>> user.)
>> The problem is-- application dependencies exist where the application
>> connects to the database server as User1, and appears to *not* fully
>> qualify
>> table access. This is a legacy application that's existed without
>> modification for years and I'm afraid can't be modified to have qualified
>> table access.
>> However, I'm stumped by the issue. I'm wondering what variables and
>> configuration settings I haven't considered yet that, on SOURCE, would
>> allow
>> me to *not* qualify table access and be OK with it, yet on DEST, seem to
>> require table qualification.
>> Any help and assistance would be greatly appreciated in advance.
>> Thanks,
>> - H-
>>
>>
>|||If what I described is actually the problem (and I do not know that it is)
then there is little that you can do about it. By design, SQL Logins are
independent between servers. If the owner of the databases is a domain
user, then the SID should be the same, but I have not played with it to
determine what other issues arise.
1 - If the database to which you restore was created with the wrong owner,
you may need to do the reset steps only once. Then, once set db is owned by
the proper account, there will be no unsynchronized dbo user inside the
database.
2 - If that does not work, include the resetting of the owner during the
restore process (script restore followed by the code to fiddle with the
owner).
3 - If the owner of the database on both servers is 'sa' then that might be
sufficient to avoid the problem since the 'sa' account always has the same
id. (But your application, like some I have seen, may demand that the owner
of the database be an application specific owner.)
It is not really a *** problem ***, just a consequence of two independent
machines.
RLF
"H Lee" <anon@.anon.com> wrote in message
news:OXb5bSr%23FHA.2464@.TK2MSFTNGP15.phx.gbl...
> Russell,
> I appreciate your effort and reply. I was wondering if you could explain
> how I might eliminate the initial backup and restore flaw.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OSx4o0p%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>>H Lee,
>> First, true confession: I did not read the whole thing and attempt to
>> understand it all. However...
>> Your process of backup and restore has a common flaw. This can result in
>> the restored database having a different owner of the database from the
>> internal user mapped as dbo. If this is your case, then you should do
>> the following:
>> sp_changedbowner @.loginame = 'NEWlogin' , @.map = 1
>> sp_changedbowner @.loginame = 'Originalogin' , @.map = 1
>> If this works, then great.
>> RLF
>>
>> "H Lee" <anon@.anon.com> wrote in message
>> news:uc6xDmp%23FHA.1288@.TK2MSFTNGP09.phx.gbl...
>>I recently migrated Database1, which contains Table1, from one SQL Server
>> 2000 box to another (Source and Dest, I'll call them). This is probably
>> the
>> 50th database migration I've performed within the past few months. My
>> general steps for migration are:
>> 1. Create and execute the sp_help_revlogin stored procedure on Source to
>> copy all the SQL Server logins from Source to Dest.
>> 2. Copy the sp_help_revlogin output and execute on Dest server.
>> 3. Backup Database1 on Source, and restore Database1 on Dest.
>> 4. Execute sp_sidmap stored procedure in Database1 on Dest, to map any
>> orphaned logins on Dest's Database1.
>> With Database1, there are many database objects, tables, created using
>> the
>> login "User1." Here's where I'm stumped.
>> 1. When I try to select from the User1-created Table1 on *SOURCE*, while
>> I'm
>> logged in as User1, using the following stmt:
>> SELECT * FROM TABLE1
>> ... I get my resultset, no issues.
>> 2. After the migration, when I try to select from the User1-created
>> Table1
>> on *DEST*, while I'm logged in as User1, using the following stmt:
>> SELECT * FROM TABLE1
>> ... I get an *error message*, "Server: Msg 208, Level 16, State 1, Line
>> 1.
>> Invalid object name 'TABLE1'" and *no resultset*.
>> At least I'm relieved then when I try fully qualifying the table
>> reference:
>> SELECT * FROM USER1.TABLE1
>> ... I get my resultset back with no error messages.
>> 3. Next, I try assigning User1 to the System Administrators server role
>> in
>> Database1 on *DEST* to try to find a way around the issue above. No
>> dice--
>> same error message. Also, viewing the Users choice under Database1 on
>> *DEST* via SQLEM shows the Name "User1" mapped to Login Name "User1."
>> Executing "sp_change_users_login 'Report'" returns 0 rows, and executing
>> "sp_change_users_login 'Update_One', 'User1', 'User1'" returns the
>> message,
>> "The number of orphaned users fixed by updating users was 0." Also,
>> trying
>> to drop User1 via SQLEM -> Security -> Logins -> Database Access tab,
>> from
>> Database1, gives me "Error 15183: The user owns objects in the database
>> and
>> cannot be dropped." (I was experimenting to see if I can drop and
>> re-add
>> user.)
>> The problem is-- application dependencies exist where the application
>> connects to the database server as User1, and appears to *not* fully
>> qualify
>> table access. This is a legacy application that's existed without
>> modification for years and I'm afraid can't be modified to have
>> qualified
>> table access.
>> However, I'm stumped by the issue. I'm wondering what variables and
>> configuration settings I haven't considered yet that, on SOURCE, would
>> allow
>> me to *not* qualify table access and be OK with it, yet on DEST, seem to
>> require table qualification.
>> Any help and assistance would be greatly appreciated in advance.
>> Thanks,
>> - H-
>>
>>
>>
>

issues using MS Access with SQL Server 2005

I'm trying to help my clients configure MS Access to run queries against a
SQL Server 2005 on a hoste providers server.
I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I get
the following error:
"This version of Microsoft Access deosn't support design changes with the
version of MS SQL Server your Access project is connected to. Your design
changes will not be saved."
My client, using Access 2003 gets a similar message and cannot save the view
she designed.
Is there something we have to configure on the hosted server to allow use of
Access?
Thanks for any assistance with this.Dabbler wrote:
> I'm trying to help my clients configure MS Access to run queries against a
> SQL Server 2005 on a hoste providers server.
> I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I get
> the following error:
> "This version of Microsoft Access deosn't support design changes with the
> version of MS SQL Server your Access project is connected to. Your design
> changes will not be saved."
> My client, using Access 2003 gets a similar message and cannot save the view
> she designed.
> Is there something we have to configure on the hosted server to allow use of
> Access?
> Thanks for any assistance with this.
Access ADP doesn't support modifications to SQL Server 2005 objects.
The easiest way to modify views is to use SQL Server Management Studio.
It seems to be unclear at the moment what the future of ADP is:
http://www.databaseadvisors.com/gazette/sqlexpress.htm
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the lowdown on this. Not sure what MS is thinking with regard to
the primary market for Access end users, I don't think SQL Server Management
Studio is appropriate for end users.
"David Portas" wrote:
> Dabbler wrote:
> > I'm trying to help my clients configure MS Access to run queries against a
> > SQL Server 2005 on a hoste providers server.
> >
> > I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I get
> > the following error:
> > "This version of Microsoft Access deosn't support design changes with the
> > version of MS SQL Server your Access project is connected to. Your design
> > changes will not be saved."
> >
> > My client, using Access 2003 gets a similar message and cannot save the view
> > she designed.
> >
> > Is there something we have to configure on the hosted server to allow use of
> > Access?
> >
> > Thanks for any assistance with this.
> Access ADP doesn't support modifications to SQL Server 2005 objects.
> The easiest way to modify views is to use SQL Server Management Studio.
> It seems to be unclear at the moment what the future of ADP is:
> http://www.databaseadvisors.com/gazette/sqlexpress.htm
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Microsoft is really screwing up by not supporting SQLS2005 design work within
ADPs. So now I'm going to purchase the older SQLS2000 instead of 2005 to
accomplish what my users require? Seems counter-advancement. At least this
will save about $4000 with std. edition. As Dabbler says, SQLS Management
Studio is not appropriate for end users.
"Dabbler" wrote:
> Thanks for the lowdown on this. Not sure what MS is thinking with regard to
> the primary market for Access end users, I don't think SQL Server Management
> Studio is appropriate for end users.
> "David Portas" wrote:
> > Dabbler wrote:
> > > I'm trying to help my clients configure MS Access to run queries against a
> > > SQL Server 2005 on a hoste providers server.
> > >
> > > I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I get
> > > the following error:
> > > "This version of Microsoft Access deosn't support design changes with the
> > > version of MS SQL Server your Access project is connected to. Your design
> > > changes will not be saved."
> > >
> > > My client, using Access 2003 gets a similar message and cannot save the view
> > > she designed.
> > >
> > > Is there something we have to configure on the hosted server to allow use of
> > > Access?
> > >
> > > Thanks for any assistance with this.
> >
> > Access ADP doesn't support modifications to SQL Server 2005 objects.
> > The easiest way to modify views is to use SQL Server Management Studio.
> >
> > It seems to be unclear at the moment what the future of ADP is:
> > http://www.databaseadvisors.com/gazette/sqlexpress.htm
> >
> > --
> > David Portas, SQL Server MVP
> >
> > Whenever possible please post enough code to reproduce your problem.
> > Including CREATE TABLE and INSERT statements usually helps.
> > State what version of SQL Server you are using and specify the content
> > of any error messages.
> >
> > SQL Server Books Online:
> > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> > --
> >
> >

issues using MS Access with SQL Server 2005

I'm trying to help my clients configure MS Access to run queries against a
SQL Server 2005 on a hoste providers server.
I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I get
the following error:
"This version of Microsoft Access deosn't support design changes with the
version of MS SQL Server your Access project is connected to. Your design
changes will not be saved."
My client, using Access 2003 gets a similar message and cannot save the view
she designed.
Is there something we have to configure on the hosted server to allow use of
Access?
Thanks for any assistance with this.Dabbler wrote:
> I'm trying to help my clients configure MS Access to run queries against a
> SQL Server 2005 on a hoste providers server.
> I'm using Access 2002 (10.6771.6735) SP3. When I try and design a view I g
et
> the following error:
> "This version of Microsoft Access deosn't support design changes with the
> version of MS SQL Server your Access project is connected to. Your design
> changes will not be saved."
> My client, using Access 2003 gets a similar message and cannot save the vi
ew
> she designed.
> Is there something we have to configure on the hosted server to allow use
of
> Access?
> Thanks for any assistance with this.
Access ADP doesn't support modifications to SQL Server 2005 objects.
The easiest way to modify views is to use SQL Server Management Studio.
It seems to be unclear at the moment what the future of ADP is:
http://www.databaseadvisors.com/gazette/sqlexpress.htm
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the lowdown on this. Not sure what MS is thinking with regard to
the primary market for Access end users, I don't think SQL Server Management
Studio is appropriate for end users.
"David Portas" wrote:

> Dabbler wrote:
> Access ADP doesn't support modifications to SQL Server 2005 objects.
> The easiest way to modify views is to use SQL Server Management Studio.
> It seems to be unclear at the moment what the future of ADP is:
> http://www.databaseadvisors.com/gazette/sqlexpress.htm
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Microsoft is really screwing up by not supporting SQLS2005 design work withi
n
ADPs. So now I'm going to purchase the older SQLS2000 instead of 2005 to
accomplish what my users require? Seems counter-advancement. At least this
will save about $4000 with std. edition. As Dabbler says, SQLS Management
Studio is not appropriate for end users.
"Dabbler" wrote:
[vbcol=seagreen]
> Thanks for the lowdown on this. Not sure what MS is thinking with regard t
o
> the primary market for Access end users, I don't think SQL Server Manageme
nt
> Studio is appropriate for end users.
> "David Portas" wrote:
>

issue with linked server connecting to Access

Hi,
I have a linked server setup to connect to an access file from a filer
server. If I am on the server computer logged in using Windows NT
authencation, the connections works. However, if I use a native SQL login
(with sys admin rights) it gave the following error:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine can
not
open the file '\\filer-la03p\Mbs\Credit Mortgage
Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The linked server has security mapping as remote user:"admin" with password
null.
I also have noticed that if I connect to the server using EM from my desktop
using NT authencation, the error ocurrs as well. In this case I will say it
is a "double-hopping" issue. But why when using a native SQL account will
have a problem?
From my understanding when using linked server to connect to an access file,
it is using the service account that starts SQL Server. I had setup all
privilages required for the SQL Service account. And as I inicated earlier,
it connects ok if I am at the server using NT authencation to log in.
I rearch online and it seems like I am not the only one who has problems
with linked server for access from other computers. Unfortunately, nobody ha
s
given the resolutions that works for the requester.
Someone please help, we are a microsoft shop and everything is microsoft.
This problem has stopped our development for the business units a great deal
.
Our clients were just bought in to convert a lot of their stuff to SQL
Server. Now they are going to back off!
Thanks if someone can respond quickly.
ChristineA few suggestions:
--Make sure that the Access mdb (and mdw) reside on a file share that
the Windows account has read/write/delete ACLs on for the files and
folders.
--Make sure that the Access mdb is totally unsecured (check the owner
property -- it should NOT say 'unknown').
--Make sure that the login you are using is Admin with an empty string
(''), not null.
--Use filemon (www.sysinternals.com) to check to see if SQLS is
getting access to the files/folders.
FWIW, linked servers to Access are a real PITA. You'd have a much
easier time if you worked it the other way around, linking to SQL
Server from the Access .mdb.
--Mary
On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
C@.discussions.microsoft.com> wrote:

>Hi,
>I have a linked server setup to connect to an access file from a filer
>server. If I am on the server computer logged in using Windows NT
>authencation, the connections works. However, if I use a native SQL login
>(with sys admin rights) it gave the following error:
>Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine ca
nnot
>open the file '\\filer-la03p\Mbs\Credit Mortgage
>Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>The linked server has security mapping as remote user:"admin" with password
>null.
>I also have noticed that if I connect to the server using EM from my deskto
p
>using NT authencation, the error ocurrs as well. In this case I will say it
>is a "double-hopping" issue. But why when using a native SQL account will
>have a problem?
>From my understanding when using linked server to connect to an access file
,
>it is using the service account that starts SQL Server. I had setup all
>privilages required for the SQL Service account. And as I inicated earlier,
>it connects ok if I am at the server using NT authencation to log in.
>I rearch online and it seems like I am not the only one who has problems
>with linked server for access from other computers. Unfortunately, nobody h
as
>given the resolutions that works for the requester.
>Someone please help, we are a microsoft shop and everything is microsoft.
>This problem has stopped our development for the business units a great dea
l.
>Our clients were just bought in to convert a lot of their stuff to SQL
>Server. Now they are going to back off!
>Thanks if someone can respond quickly.
>Christine
>|||Mary,
Thanks for replying.
All the checklists you suggested are all there and correct, otherwise it
wouldn't have worked for some cases. It would just not work AT ALL.
I have found the fix for one issue now, but not another:
1. After I added the spn for the server to the AD account that started SQL
Server, I was able to connect from my desktop to the linked server through N
T
authencation.
Before this is only possible if I am at the server.
2. If I connect to SQL Server using SQL accounts, it still doesn't work on
either from the server or my desktop. So I was wondering if I login as 'sa',
what kind of security context that was verified against the file server on
the network? After it gets through the file system then the security check
will use "Admin" to open Access as specified in my linked server security
tab. But what happened before then?
I would assume either way (login as SQL or NT authencation to my SQL Server)
it should use the service account that starts SQL Server, but apparently it
is not the case.
I am really puzzled now...
"Mary Chipman" wrote:

> A few suggestions:
> --Make sure that the Access mdb (and mdw) reside on a file share that
> the Windows account has read/write/delete ACLs on for the files and
> folders.
> --Make sure that the Access mdb is totally unsecured (check the owner
> property -- it should NOT say 'unknown').
> --Make sure that the login you are using is Admin with an empty string
> (''), not null.
> --Use filemon (www.sysinternals.com) to check to see if SQLS is
> getting access to the files/folders.
> FWIW, linked servers to Access are a real PITA. You'd have a much
> easier time if you worked it the other way around, linking to SQL
> Server from the Access .mdb.
> --Mary
> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
> C@.discussions.microsoft.com> wrote:
>
>|||What did filemon show?
--Mary
On Thu, 14 Oct 2004 16:31:02 -0700, "Christine C"
<ChristineC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Mary,
>Thanks for replying.
>All the checklists you suggested are all there and correct, otherwise it
>wouldn't have worked for some cases. It would just not work AT ALL.
>I have found the fix for one issue now, but not another:
>1. After I added the spn for the server to the AD account that started SQL
>Server, I was able to connect from my desktop to the linked server through
NT
>authencation.
>Before this is only possible if I am at the server.
>2. If I connect to SQL Server using SQL accounts, it still doesn't work on
>either from the server or my desktop. So I was wondering if I login as 'sa'
,
>what kind of security context that was verified against the file server on
>the network? After it gets through the file system then the security check
>will use "Admin" to open Access as specified in my linked server security
>tab. But what happened before then?
>I would assume either way (login as SQL or NT authencation to my SQL Server
)
>it should use the service account that starts SQL Server, but apparently it
>is not the case.
>I am really puzzled now...
>
>"Mary Chipman" wrote:
>

issue with linked server connecting to Access

Hi,
I have a linked server setup to connect to an access file from a filer
server. If I am on the server computer logged in using Windows NT
authencation, the connections works. However, if I use a native SQL login
(with sys admin rights) it gave the following error:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\filer-la03p\Mbs\Credit Mortgage
Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The linked server has security mapping as remote user:"admin" with password
null.
I also have noticed that if I connect to the server using EM from my desktop
using NT authencation, the error ocurrs as well. In this case I will say it
is a "double-hopping" issue. But why when using a native SQL account will
have a problem?
From my understanding when using linked server to connect to an access file,
it is using the service account that starts SQL Server. I had setup all
privilages required for the SQL Service account. And as I inicated earlier,
it connects ok if I am at the server using NT authencation to log in.
I rearch online and it seems like I am not the only one who has problems
with linked server for access from other computers. Unfortunately, nobody has
given the resolutions that works for the requester.
Someone please help, we are a microsoft shop and everything is microsoft.
This problem has stopped our development for the business units a great deal.
Our clients were just bought in to convert a lot of their stuff to SQL
Server. Now they are going to back off!
Thanks if someone can respond quickly.
Christine
A few suggestions:
--Make sure that the Access mdb (and mdw) reside on a file share that
the Windows account has read/write/delete ACLs on for the files and
folders.
--Make sure that the Access mdb is totally unsecured (check the owner
property -- it should NOT say 'unknown').
--Make sure that the login you are using is Admin with an empty string
(''), not null.
--Use filemon (www.sysinternals.com) to check to see if SQLS is
getting access to the files/folders.
FWIW, linked servers to Access are a real PITA. You'd have a much
easier time if you worked it the other way around, linking to SQL
Server from the Access .mdb.
--Mary
On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
C@.discussions.microsoft.com> wrote:

>Hi,
>I have a linked server setup to connect to an access file from a filer
>server. If I am on the server computer logged in using Windows NT
>authencation, the connections works. However, if I use a native SQL login
>(with sys admin rights) it gave the following error:
>Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>open the file '\\filer-la03p\Mbs\Credit Mortgage
>Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>The linked server has security mapping as remote user:"admin" with password
>null.
>I also have noticed that if I connect to the server using EM from my desktop
>using NT authencation, the error ocurrs as well. In this case I will say it
>is a "double-hopping" issue. But why when using a native SQL account will
>have a problem?
>From my understanding when using linked server to connect to an access file,
>it is using the service account that starts SQL Server. I had setup all
>privilages required for the SQL Service account. And as I inicated earlier,
>it connects ok if I am at the server using NT authencation to log in.
>I rearch online and it seems like I am not the only one who has problems
>with linked server for access from other computers. Unfortunately, nobody has
>given the resolutions that works for the requester.
>Someone please help, we are a microsoft shop and everything is microsoft.
>This problem has stopped our development for the business units a great deal.
>Our clients were just bought in to convert a lot of their stuff to SQL
>Server. Now they are going to back off!
>Thanks if someone can respond quickly.
>Christine
>
|||Mary,
Thanks for replying.
All the checklists you suggested are all there and correct, otherwise it
wouldn't have worked for some cases. It would just not work AT ALL.
I have found the fix for one issue now, but not another:
1. After I added the spn for the server to the AD account that started SQL
Server, I was able to connect from my desktop to the linked server through NT
authencation.
Before this is only possible if I am at the server.
2. If I connect to SQL Server using SQL accounts, it still doesn't work on
either from the server or my desktop. So I was wondering if I login as 'sa',
what kind of security context that was verified against the file server on
the network? After it gets through the file system then the security check
will use "Admin" to open Access as specified in my linked server security
tab. But what happened before then?
I would assume either way (login as SQL or NT authencation to my SQL Server)
it should use the service account that starts SQL Server, but apparently it
is not the case.
I am really puzzled now...
"Mary Chipman" wrote:

> A few suggestions:
> --Make sure that the Access mdb (and mdw) reside on a file share that
> the Windows account has read/write/delete ACLs on for the files and
> folders.
> --Make sure that the Access mdb is totally unsecured (check the owner
> property -- it should NOT say 'unknown').
> --Make sure that the login you are using is Admin with an empty string
> (''), not null.
> --Use filemon (www.sysinternals.com) to check to see if SQLS is
> getting access to the files/folders.
> FWIW, linked servers to Access are a real PITA. You'd have a much
> easier time if you worked it the other way around, linking to SQL
> Server from the Access .mdb.
> --Mary
> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
> C@.discussions.microsoft.com> wrote:
>
>
|||What did filemon show?
--Mary
On Thu, 14 Oct 2004 16:31:02 -0700, "Christine C"
<ChristineC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Mary,
>Thanks for replying.
>All the checklists you suggested are all there and correct, otherwise it
>wouldn't have worked for some cases. It would just not work AT ALL.
>I have found the fix for one issue now, but not another:
>1. After I added the spn for the server to the AD account that started SQL
>Server, I was able to connect from my desktop to the linked server through NT
>authencation.
>Before this is only possible if I am at the server.
>2. If I connect to SQL Server using SQL accounts, it still doesn't work on
>either from the server or my desktop. So I was wondering if I login as 'sa',
>what kind of security context that was verified against the file server on
>the network? After it gets through the file system then the security check
>will use "Admin" to open Access as specified in my linked server security
>tab. But what happened before then?
>I would assume either way (login as SQL or NT authencation to my SQL Server)
>it should use the service account that starts SQL Server, but apparently it
>is not the case.
>I am really puzzled now...
>
>"Mary Chipman" wrote:

issue with linked server connecting to Access

Hi,
I have a linked server setup to connect to an access file from a filer
server. If I am on the server computer logged in using Windows NT
authencation, the connections works. However, if I use a native SQL login
(with sys admin rights) it gave the following error:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\filer-la03p\Mbs\Credit Mortgage
Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The linked server has security mapping as remote user:"admin" with password
null.
I also have noticed that if I connect to the server using EM from my desktop
using NT authencation, the error ocurrs as well. In this case I will say it
is a "double-hopping" issue. But why when using a native SQL account will
have a problem?
From my understanding when using linked server to connect to an access file,
it is using the service account that starts SQL Server. I had setup all
privilages required for the SQL Service account. And as I inicated earlier,
it connects ok if I am at the server using NT authencation to log in.
I rearch online and it seems like I am not the only one who has problems
with linked server for access from other computers. Unfortunately, nobody has
given the resolutions that works for the requester.
Someone please help, we are a microsoft shop and everything is microsoft.
This problem has stopped our development for the business units a great deal.
Our clients were just bought in to convert a lot of their stuff to SQL
Server. Now they are going to back off!
Thanks if someone can respond quickly.
ChristineA few suggestions:
--Make sure that the Access mdb (and mdw) reside on a file share that
the Windows account has read/write/delete ACLs on for the files and
folders.
--Make sure that the Access mdb is totally unsecured (check the owner
property -- it should NOT say 'unknown').
--Make sure that the login you are using is Admin with an empty string
(''), not null.
--Use filemon (www.sysinternals.com) to check to see if SQLS is
getting access to the files/folders.
FWIW, linked servers to Access are a real PITA. You'd have a much
easier time if you worked it the other way around, linking to SQL
Server from the Access .mdb.
--Mary
On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
C@.discussions.microsoft.com> wrote:
>Hi,
>I have a linked server setup to connect to an access file from a filer
>server. If I am on the server computer logged in using Windows NT
>authencation, the connections works. However, if I use a native SQL login
>(with sys admin rights) it gave the following error:
>Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>open the file '\\filer-la03p\Mbs\Credit Mortgage
>Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>The linked server has security mapping as remote user:"admin" with password
>null.
>I also have noticed that if I connect to the server using EM from my desktop
>using NT authencation, the error ocurrs as well. In this case I will say it
>is a "double-hopping" issue. But why when using a native SQL account will
>have a problem?
>From my understanding when using linked server to connect to an access file,
>it is using the service account that starts SQL Server. I had setup all
>privilages required for the SQL Service account. And as I inicated earlier,
>it connects ok if I am at the server using NT authencation to log in.
>I rearch online and it seems like I am not the only one who has problems
>with linked server for access from other computers. Unfortunately, nobody has
>given the resolutions that works for the requester.
>Someone please help, we are a microsoft shop and everything is microsoft.
>This problem has stopped our development for the business units a great deal.
>Our clients were just bought in to convert a lot of their stuff to SQL
>Server. Now they are going to back off!
>Thanks if someone can respond quickly.
>Christine
>|||Mary,
Thanks for replying.
All the checklists you suggested are all there and correct, otherwise it
wouldn't have worked for some cases. It would just not work AT ALL.
I have found the fix for one issue now, but not another:
1. After I added the spn for the server to the AD account that started SQL
Server, I was able to connect from my desktop to the linked server through NT
authencation.
Before this is only possible if I am at the server.
2. If I connect to SQL Server using SQL accounts, it still doesn't work on
either from the server or my desktop. So I was wondering if I login as 'sa',
what kind of security context that was verified against the file server on
the network? After it gets through the file system then the security check
will use "Admin" to open Access as specified in my linked server security
tab. But what happened before then?
I would assume either way (login as SQL or NT authencation to my SQL Server)
it should use the service account that starts SQL Server, but apparently it
is not the case.
I am really puzzled now...
"Mary Chipman" wrote:
> A few suggestions:
> --Make sure that the Access mdb (and mdw) reside on a file share that
> the Windows account has read/write/delete ACLs on for the files and
> folders.
> --Make sure that the Access mdb is totally unsecured (check the owner
> property -- it should NOT say 'unknown').
> --Make sure that the login you are using is Admin with an empty string
> (''), not null.
> --Use filemon (www.sysinternals.com) to check to see if SQLS is
> getting access to the files/folders.
> FWIW, linked servers to Access are a real PITA. You'd have a much
> easier time if you worked it the other way around, linking to SQL
> Server from the Access .mdb.
> --Mary
> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
> C@.discussions.microsoft.com> wrote:
> >Hi,
> >
> >I have a linked server setup to connect to an access file from a filer
> >server. If I am on the server computer logged in using Windows NT
> >authencation, the connections works. However, if I use a native SQL login
> >(with sys admin rights) it gave the following error:
> >
> >Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
> >
> >OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> >
> >[OLE/DB provider returned message: The Microsoft Jet database engine cannot
> >open the file '\\filer-la03p\Mbs\Credit Mortgage
> >Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
> >exclusively by another user, or you need permission to view its data.]
> >
> >OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> >IDBInitialize::Initialize returned 0x80004005: ].
> >
> >The linked server has security mapping as remote user:"admin" with password
> >null.
> >I also have noticed that if I connect to the server using EM from my desktop
> >using NT authencation, the error ocurrs as well. In this case I will say it
> >is a "double-hopping" issue. But why when using a native SQL account will
> >have a problem?
> >
> >From my understanding when using linked server to connect to an access file,
> >it is using the service account that starts SQL Server. I had setup all
> >privilages required for the SQL Service account. And as I inicated earlier,
> >it connects ok if I am at the server using NT authencation to log in.
> >
> >I rearch online and it seems like I am not the only one who has problems
> >with linked server for access from other computers. Unfortunately, nobody has
> >given the resolutions that works for the requester.
> >
> >Someone please help, we are a microsoft shop and everything is microsoft.
> >This problem has stopped our development for the business units a great deal.
> >Our clients were just bought in to convert a lot of their stuff to SQL
> >Server. Now they are going to back off!
> >
> >Thanks if someone can respond quickly.
> >
> >Christine
> >
>|||What did filemon show?
--Mary
On Thu, 14 Oct 2004 16:31:02 -0700, "Christine C"
<ChristineC@.discussions.microsoft.com> wrote:
>Mary,
>Thanks for replying.
>All the checklists you suggested are all there and correct, otherwise it
>wouldn't have worked for some cases. It would just not work AT ALL.
>I have found the fix for one issue now, but not another:
>1. After I added the spn for the server to the AD account that started SQL
>Server, I was able to connect from my desktop to the linked server through NT
>authencation.
>Before this is only possible if I am at the server.
>2. If I connect to SQL Server using SQL accounts, it still doesn't work on
>either from the server or my desktop. So I was wondering if I login as 'sa',
>what kind of security context that was verified against the file server on
>the network? After it gets through the file system then the security check
>will use "Admin" to open Access as specified in my linked server security
>tab. But what happened before then?
>I would assume either way (login as SQL or NT authencation to my SQL Server)
>it should use the service account that starts SQL Server, but apparently it
>is not the case.
>I am really puzzled now...
>
>"Mary Chipman" wrote:
>> A few suggestions:
>> --Make sure that the Access mdb (and mdw) reside on a file share that
>> the Windows account has read/write/delete ACLs on for the files and
>> folders.
>> --Make sure that the Access mdb is totally unsecured (check the owner
>> property -- it should NOT say 'unknown').
>> --Make sure that the login you are using is Admin with an empty string
>> (''), not null.
>> --Use filemon (www.sysinternals.com) to check to see if SQLS is
>> getting access to the files/folders.
>> FWIW, linked servers to Access are a real PITA. You'd have a much
>> easier time if you worked it the other way around, linking to SQL
>> Server from the Access .mdb.
>> --Mary
>> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
>> C@.discussions.microsoft.com> wrote:
>> >Hi,
>> >
>> >I have a linked server setup to connect to an access file from a filer
>> >server. If I am on the server computer logged in using Windows NT
>> >authencation, the connections works. However, if I use a native SQL login
>> >(with sys admin rights) it gave the following error:
>> >
>> >Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>> >
>> >OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>> >
>> >[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>> >open the file '\\filer-la03p\Mbs\Credit Mortgage
>> >Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>> >exclusively by another user, or you need permission to view its data.]
>> >
>> >OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>> >IDBInitialize::Initialize returned 0x80004005: ].
>> >
>> >The linked server has security mapping as remote user:"admin" with password
>> >null.
>> >I also have noticed that if I connect to the server using EM from my desktop
>> >using NT authencation, the error ocurrs as well. In this case I will say it
>> >is a "double-hopping" issue. But why when using a native SQL account will
>> >have a problem?
>> >
>> >From my understanding when using linked server to connect to an access file,
>> >it is using the service account that starts SQL Server. I had setup all
>> >privilages required for the SQL Service account. And as I inicated earlier,
>> >it connects ok if I am at the server using NT authencation to log in.
>> >
>> >I rearch online and it seems like I am not the only one who has problems
>> >with linked server for access from other computers. Unfortunately, nobody has
>> >given the resolutions that works for the requester.
>> >
>> >Someone please help, we are a microsoft shop and everything is microsoft.
>> >This problem has stopped our development for the business units a great deal.
>> >Our clients were just bought in to convert a lot of their stuff to SQL
>> >Server. Now they are going to back off!
>> >
>> >Thanks if someone can respond quickly.
>> >
>> >Christine
>> >
>>

Issue when setting up the XML connection to Analysis Services on W

I am using XML For Analysis in a Windows 2000 server in order to access my
OLAP cubes and I'm getting the following error when using the Sample VB app
that comes with the package:
Run-time error '500'
<FaultCode>1198808394</FaultCode>
<Description></Description>
Can some one help me?You may want to ask this question in the analysis services group.
Best regards
Michael
"Carlos La Rovere" <Carlos La Rovere@.discussions.microsoft.com> wrote in
message news:F602ABDF-A418-49A5-B6A3-CA36C3DBE102@.microsoft.com...
>I am using XML For Analysis in a Windows 2000 server in order to access my
> OLAP cubes and I'm getting the following error when using the Sample VB
> app
> that comes with the package:
> Run-time error '500'
> <FaultCode>1198808394</FaultCode>
> <Description></Description>
> Can some one help me?

Issue Resolved - Interaction between SQL Server and Access

I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.

Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.

Yes, that is expected behavior.

Permissions in SQL Server have three values: GRANT, DENY, or 'unsaid'.

If you have been GRANTed permission for a table, obviously you have permission.

If your permission is 'unsaid', then you 'may' still have permission due to permission having been granted to another role that includes you.

But IF you have been explicited DENY(ied), that 'trumps' all.

Think of it this way.

Children have a knack of knowing how to 'scope out' their parents. Perhaps a son wants to go out with friends. He may approach Mom and 'feel her out' to find out if she 'might' say yes WITHOUT directly asking her. He knows that if he asks her and she says 'No', his plans are shot because he cannot then go and ask Dad (DENY). So he will attempt to find out if it is 'safe' to ask her. If it seems safe, he will ask and he's 'home free' (GRANT).

However, if he feels that she would probably say No, then without having asked, he is now free to ask Dad. So Mom was 'unsaid', if permission can be had by another route, it will work for him.

So anytime permission is an explicit DENY, there is no route around it.

Often, in a strong SQL Server security model, TABLE permissions are left 'unsaid', and access is GRANTed through VIEWS, Functions, and Stored Procedures. Users are also added to the db_DenyDataReader and db_DenyDataWriter roles to prohibit them having direct table access.

|||

Thanks for responding.

Ah, yes, that's what I thought. But if I leave the permissions on the base table "unsaid", and grant all on the UDF, Access tells me that the recordset is not updatable (maybe because it can't "see" the PK column?). So I'm back to having to grant permissions in the base table, which is unacceptable.

I read through the good whitepaper on row-level security by Rask, Rubin and Neumann. The architecture they propose is great, but overkill for what I need to do. Nevertheless, I set up a test using their methodology: DENY ALL on the base table, GRANT ALL on a view, and put an INSTEAD OF trigger on the view to verify appropriate access and perform an update. But because Access thinks the recordset isn't updatable, the trigger never fires. If I grant SELECT permissions to just the PK column of the base table, Access thinks the recordset is updatable, but I still can't get the trigger to fire because Access wants at least SELECT permissions on the other base table columns before it will even try to perform the update.

|||

I would suggest the following topics from BOL:

· CREATE VIEW (http://msdn2.microsoft.com/en-us/library/ms187956.aspx), got to the section Updatable Views

· Modifying Data Through a View (http://msdn2.microsoft.com/en-us/library/ms180800.aspx)

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

In addition to Raul's suggestions, I offer the following insight.

Access will allow you to UPDATE or DELETE a row without the table having a primary key (or some unique identifier.)

SQL Server does NOT allow UPDATES or DELETES unless there is a unambiguous way to be certain what row is being addressed. If the VIEW does not include a PK, or unique identifier, it would not be updatable.

|||Thanks for the references, I'll read through them and see where they lead. I note that these are from SQL Server 2005 BOL, and the server that must host the application I'm working with is SQL Server 2000 SP4. I'm just wondering whether you know whether the support for updatable views changed between SQL 2000 and 2005?|||

As far as I understand, updatable views should be supported in SQL Server 2000 SP4, but I am not 100% sure if all the documentation in the links I included may apply to SQL Server 2000 SP4 as well.

I would recommend trying to find the same topic in BOL fro SQL Server 2000 and giving it a try; if you have any further questions please let us know, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks to both you and Arnie for responding to this inquiry.

The problem turns out to have been an interaction between SQL Server and Access. In order for Access to update a SQL Server view, the view must be declared the WITH VIEW_METADATA option. If designing the view in Access, this is accomplished by checking the view option "Update using view rules" on the properties page. Once I did this, I was able to DENY ALL on the base table and GRANT ALL on the view, and the view was updatable via Access with appropriate security.

I ran a SQL Profiler trace to see what Access was sending to SQL Server when the option was properly set--it showed that the UPDATE statement Access generated was against the view, not the base tables. Without using the VIEW_METADATA option, Access does not consider the recordset updatable (hence my original question), so it doesn't generate an UPDATE statement. So I couldn't run a trace to see what happens in that case. I expect that if I could, the UPDATE would be going against the base table rather than the view.

Again, thanks for your help.

Issue on SQL in MS SQL2000 and Access

I am not a programmer so anyone who can help, please do.

I have downloaded an application written for MS SQL2000 but I want to use my desktop for testing and configuration where I have Access and XP Pro IIS. I was told that the SQL in MS SQL2000 is not the same as SQL in Access, so I will face problem even if I change the connection string to point to Access instead of MS SQL2000.

Is this true? If yes, how can I change the SQLs so that it works with Access?Yes, it is decidely true. While Jet (the database engine that MS-Access uses by default) uses an SQL-like language, it isn't the real McCoy. MS-SQL implements nearly all of the SQL-92 standard, and plays by the rules nearly all of the time.

With the Professional version of MS-Access, you get MSDE included on the CD, but not automajikally installed. You can install it (and I'd strongly recommend applying Service Pack 3), and it will give you almost exactly the same functionality as MS-SQL, but pared down to run on a workstation.

-PatP|||Does that mean if I buy a MS Office Pro (which I supposed have MSDE in it) and install the software in my desktop, it should run fine? Or should I buy other software from MS?

And you mentioned the MSDE not installed automatically, how do I then installed it?|||Operative word being should, yes, the application should work using MSDE.

MSDE is included in all of the Professional versions of MS-Access, including the one that ships as part of MS-Office Professional.

The MSDE installation instructions vary slightly from one product to another. To install the version shipped with MS-Office, you double click the SETUP icon in the MSDE folder on the CD.

-PatP|||Thanks again for the information.

Just to further my knowledge on SQL, do you have samples (or do you know a website that has samples) that shows the differences between the SQL in an Access query and that in MS SQL2000?

It would be nice to know some basic differences....

Issue in Custom Security Extension

Hi,

The custom security extension generates the following error, when try to access Report Manager. The specifications are as follows.
1. Its 64-bit SQL Server (Reporting Services) and installed as default instance.
2. The Report Server is accessible programmatically. I can publish, view reports without problems, but Report Manager gives an error.
3. Another instance (named instance) of Reporting Services with the same custom security extension on same server is working fine. Report Manager opens up without problems.
4. Both the instances working fine since 3 installation and configuration (3 months back). Now, only the Report Manager of the default instance is not openeing up.
ReportServerWebApp__08_22_2006_08_52_20.log
-
w3wp!ui!1!8/22/2006-08:52:56:: i INFO: Overwriting existing cookie
w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: The report server is not responding. Verify that the report server is running and can be accessed from this computer.
w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: HTTP status code --> 500
-Details--
Microsoft.ReportingServices.UI.Global+RSWebServiceWrapper+CantCommunicateWithReportServerException: The report server is not responding. Verify that the report server is running and can be accessed from this computer.

at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()

at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)

at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()

at Microsoft.ReportingServices.UI.Global.SecureAllAPI()

at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level)

at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args)

at System.EventHandler.Invoke(Object sender, EventArgs e)

at System.Web.UI.Control.OnInit(EventArgs e)

at System.Web.UI.Page.OnInit(EventArgs e)

at System.Web.UI.Control.InitRecursive(Control namingContainer)

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
w3wp!ui!1!8/22/2006-08:52:57:: i INFO: Overwriting existing cookie
w3wp!ui!1!8/22/2006-08:52:58:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)

The service is running fine. Any suggestions/pointers would be greatly appreciated.

THanks in advance

Is there anyway to trace and find the error. Its URGENT!!! Can anyone suggest ?|||

Does anyone has the solution of above problem ? It's urgent.

|||

The error indicates report manager has problem talking to report server soap endpoint. Please check RSWebApplication.config under report manager to make sure either ReportServerUrl or ReportServerVirtualDirectory contains the correct value.

If you think you have the right value in config file, you can install a http tracer between report manager and report server so you can really see the traffic between them.

Issue in Custom Security Extension

Hi,

The custom security extension generates the following error, when try to access Report Manager. The specifications are as follows.
1. Its 64-bit SQL Server (Reporting Services) and installed as default instance.
2. The Report Server is accessible programmatically. I can publish, view reports without problems, but Report Manager gives an error.
3. Another instance (named instance) of Reporting Services with the same custom security extension on same server is working fine. Report Manager opens up without problems.
4. Both the instances working fine since 3 installation and configuration (3 months back). Now, only the Report Manager of the default instance is not openeing up.
ReportServerWebApp__08_22_2006_08_52_20.log
-
w3wp!ui!1!8/22/2006-08:52:56:: i INFO: Overwriting existing cookie
w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: The report server is not responding. Verify that the report server is running and can be accessed from this computer.
w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: HTTP status code --> 500
-Details--
Microsoft.ReportingServices.UI.Global+RSWebServiceWrapper+CantCommunicateWithReportServerException: The report server is not responding. Verify that the report server is running and can be accessed from this computer.

at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()

at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)

at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()

at Microsoft.ReportingServices.UI.Global.SecureAllAPI()

at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level)

at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args)

at System.EventHandler.Invoke(Object sender, EventArgs e)

at System.Web.UI.Control.OnInit(EventArgs e)

at System.Web.UI.Page.OnInit(EventArgs e)

at System.Web.UI.Control.InitRecursive(Control namingContainer)

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
w3wp!ui!1!8/22/2006-08:52:57:: i INFO: Overwriting existing cookie
w3wp!ui!1!8/22/2006-08:52:58:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)

The service is running fine. Any suggestions/pointers would be greatly appreciated.

THanks in advance

Is there anyway to trace and find the error. Its URGENT!!! Can anyone suggest ?|||

Does anyone has the solution of above problem ? It's urgent.

|||

The error indicates report manager has problem talking to report server soap endpoint. Please check RSWebApplication.config under report manager to make sure either ReportServerUrl or ReportServerVirtualDirectory contains the correct value.

If you think you have the right value in config file, you can install a http tracer between report manager and report server so you can really see the traffic between them.