Hello,

Our .NET application runs in a shared environment managed by and ISP who
hosts .NET applications. The SQL Server box houses several different
databases for their clients (presently 123 different databases on one
production box). Recently, when trying to DTS table changes from our
development SqlServer environ to the shared SqlServer environ, we started
getting errors saying that our user authentication was not valid for a
completely different database. Eventhough we are targeting the correct
target Database, it "Thinks" we are trying to add these tables to a
completely different database on that box. The database it says we don't
have access to is in fact on that box.

Can someone at least point us in a direction so we can tell our ISP? They
seem to be really slow about getting to the root of the problem.

Thanks in advance,

Paul Reed

Perhaps

Can you , using the same credentials, log onto the correct server/database
combo using QA?

If no and you still end up on the wrong database then maybe what is
happening is that the DEFAULT database for your login has been set to the
other DB but not given any permissions for it.

If you can log on successfully to the correct DB/Server combo in QA then I
don't see what the problem would be

Your ISP should have granted your login permissions only to your DB and made
it your default DB also.

In DTS when you authenticate the connection(create it) then try to set up a
datapump is this when it happens (the authentication error)?



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"paul reed" <prreed (AT) jacksonreed (DOT) com> wrote

In message <uhhRjV38DHA.1672 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, paul reed
<prreed (AT) jacksonreed (DOT) com> writes
When changing the DB a package uses make sure you change both the
connection DB, and also check any DataPump and DDQ Tasks, for the source
and destination object names. These normally hold the three part name,
which will override the connection DB setting.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Allan,

Thanks for your reply. I can see our tables just fine via QA...I can
query them, etc...If I try to access the database that it says I am
trying to update when I do a DTS, it won't allow me to see it. Which is
what I would expect it to do.

It is only when I try to do a DTS package to the server that I get this
error...again telling me I don't have authorization to work on this
database...that I am NOT targeting. The scary part is, sometimes we get
this error and sometimes we don't. And even worse, when the DTS package
does run, the tables at the target empty. Yet I can take this same DTS
package and target a different SQL box (a non shared server that is on
our environ and not the ISP)...and everything works just fine. This is
not a fancy DTS package...5 tables with their data...using all the
default options. We have done this 100's and 100's of times before
against this ISP with not a hint of a problem.

Thanks

Paul Reed
www.jacksonreed.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Strange

Sometimes it does work sometimes not (Could still be the ISP I guess)
So when you build the package you add a SQL Server connection which points
to ISP_Server and ISP_GoodDB
You then build a datapump using this connection and one pointing to Local_DB
and Local_Server
When you go to specify the properties of the datapump it errors with the
message about permissions on ISP_BADDB? Or you correctly set up the package
and the failure only happens when you press GO?

The thing that would get me is that it sometimes works and sometimes fails.
I presume you change nothing so what does? Unless the data access layer in
the middle goes bent then shouldn't it be the ISP?

Have you tried accessing the DB through QA immediately after the failure of
the package?

Have your ISP trace the Server at the time you start the package and impress
upon them that you ARE targetting GoodDB and NOT BADDB.

When it fails with the permissions problem in the BADDB they should be able
to see it.

What is the exact error you receive?

Also you meantion that before you pump data to the ISP you clear down tables
first and this works. How are you doing that?

Using an ExecuteSQL task you have to specify a connection which means it
must be pointing to the correct DB (Good_DB) as they do get emptied.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Paul Reed" <prreed (AT) jacksonreed (DOT) com> wrote

Hi Paul,

We haven't heard back from you yet and I'm just writing in to see if you
have had an opportunity to collect the information.

Thanks for Darren and Allan's efforts on your issue. We need to narrow down
this random issue first. Could you test the issue with QA to see if you can
access database when your DTS package fails. Please provide us with any
error message or trace information you can get so that we can address the
problem and provide the possible solution to this issue.

Looking forward to hearing from you!

Best regards,

Billy Yao
Microsoft Online Support

Billy,

Yes...I can access the DB via QA after the DTS fails.

The webmaster at our ISP is trying something. This server had to be
restored about 30 days ago. We think this problem started after that.
They think it is a permissions problem and how the owner is setup. They
tried something last night and I am going to see if it fixed it. If it
did, I will post what they did on this thread.

Thanks for all your help and I will get back.

Paul Reed
www.jacksonreed.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!