Access Denied - Remote connection to SSIS

We have 2 SQL2005 servers currently. 1 is a dev box, the other production. To grant remote access to SSIS, I added my developers to the local Distributed COM Users group and also gave them remote Access and remote Activation on the MsDtsServer component.

This worked just fine. Recently we applied SP1 and the follow-on hotfixes to all SQL components. I followed the install order prescribed in the Q article and applied all component fixes. My developers are now unable to access SSIS remotely unless I add them to the local administrator's group. I don't see anywhere that a log gets generated to help me track down what's going on. Nothing shows up in my Windows security log, despite enabling audit logging on: Accout Logon Events, Logon Event, Object Access, and Privilege Use.

Is this a know issue in SP1 or the hotfixes? I'm hesitant to apply the patches to my production box until I can resolve this issue.

Thanks
Steve

With the lack of responses I'm assuming I'm out of luck. Anyone have suggestions on how to troubleshoot this? Is there some reg entry I can flip to enable debugging or even use of the event log for SSIS?

Thanks

|||When you say you have added developers to DCOM Users Groups, is this what you have done - Connecting to a Remote Integration Services Server (17th July 2006) - http://msdn2.microsoft.com/en-us/library/aa337083.aspx ?|||

That article only mentions the need to give them access to the msdtsserver. There is another article that also mentions that they must be placed in the local Windows group called: "Distributed COM Users". I have followed both articles. As I mentioned, this configuration was working until a short time after applying the SP1 and Hotfix patches for SQL2005. It is still working on our production box that has not yet been patched.

Thanks

Steve

|||

I am getting the same behaviour, we have also got SP1 installed.

|||

After applying SP1 and the Hotfixes, we never rebooted Windows (never received a notification that we should). This weekend we applied our monthly round of OS patches. Now that the server has been rebooted access is working correctly again.

I guess the lesson here is that, if you've followed all of the instructions and they still don't work, do a full reboot - restarting services doesn't appear to be enough.

You may consider this thread closed.

Thanks

Steve

Access DB

I am trying to connect to an access database using Reportin Services 2000.
I can develop the reports just fine on my local computer, and I can get to
the file from the server. However, when I try to connect I get a path not
found error. I tried to connect with no username password and using admin,
but again I get an invalid path. So I tried to set up a DSN. It worked
just fine, but when I reference the ODBC drivers and the DSN I get no ISAM
drivers installed and path errors as well. I am logged into the machine as
admin and have full rights.
Any suggestions?
John WrightNevermind I found the problem.
John
"John Wright" <riley_wright@.notmail.com> wrote in message
news:%23pccxDtbGHA.4716@.TK2MSFTNGP03.phx.gbl...
>I am trying to connect to an access database using Reportin Services 2000.
>I can develop the reports just fine on my local computer, and I can get to
>the file from the server. However, when I try to connect I get a path not
>found error. I tried to connect with no username password and using admin,
>but again I get an invalid path. So I tried to set up a DSN. It worked
>just fine, but when I reference the ODBC drivers and the DSN I get no ISAM
>drivers installed and path errors as well. I am logged into the machine as
>admin and have full rights.
> Any suggestions?
> John Wright
>

Access database problem

Hi, I have problem connecting SQL2000 database.

I have installed .NetFramework 1.1 before and I got (local)\NetSDK instance name. I can connect any database under without any problem. However, I recently installed another database "Costoco" into the same instance from a bak file. And I try to connect it but it fails. I run a webapplication and use datagrid to show a table and the error message is following:

Cannot open database requested in login 'Costoco'. Login fails. Login failed for user 'AARON-HOME\ASPNET'

I checked ASPNET user and I even add this user to access the whole folder of the instance in Program files/SQLserver, it still happens.

I have no idea, and could any one help me?

Thanks.

It seems that the login have not been granted access to the new database. Please click 'Start'->'All Programs'->'Microsoft SQL Server'->'Enterprise Manager'. In EM, choose the 'NetSDK'instance, explore Security->Logins, find the login used in your application, double click it. In the Properties window, goto Data Access pannel, make sure the check box of the new database selected. Click 'ok' to save your change, then try again.|||Thanks a lot, It works!!!

Access database from a different server

Hi,
i want to know, is it possible to access a database that is not on the local machine ?
what if i want to access a databse that resides on another of my websites (on a different server) ?
can any one help ?As long as the other server is set up as a linked server in sql:
select * from servername.database.dbo.table

Accesing Reports from outside the domain

How do I set it up so that people who are inside my local network, but
outside my domain to get access to my reports.
Any thoughts would be great
JeffCan't you just add the individuals to the security of the report...
e.g. DOMAIN1\User
I have reports with 3 or 4 different domain users having access.
Craig
"JeffreyWowk" <JeffreyWowk@.discussions.microsoft.com> wrote in message
news:78D313D8-0C25-4BB9-8AB5-A4BB3E4438DE@.microsoft.com...
> How do I set it up so that people who are inside my local network, but
> outside my domain to get access to my reports.
> Any thoughts would be great
> Jeff

about using osql BACKUP/RESTORE commands

Hi!
I'm using MSDE 2000 SP4 on local computer.
I backed up database into local hard drive using osql command like...
OSQL -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak'" >
LogBackupDatabase.log
...and it's working fine. But when I try to save backup file directly
into network drive, it's not working. "LogBackupDatabase.log"-file
contains error message...
"Msg 3201, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
Cannot open backup device 'Z:\MyFolder\MYDB.bak'. Device error or device
off-line. See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
BACKUP DATABASE is terminating abnormally."
Z: is mapped network drive.
Is it possible to save backup file into network drive somehow? Maybe my
osql is not correctly formed to save into backup file into network
drive, or am I missing something?
Also restore command using osql is not working if I try to restore
backup file from network drive.
Thanks in advance!
Mika
Hi,
Use UNC (REMOTESERVER\SHARE\PATH\) path along with Backup or Restore
commands.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharenameX\dbname.bak' with
init
Perform the same for Restore Database
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
Thanks
Hari
SQL Server MVP
"Mika M" <mahmik_removethis@.luukku.com> wrote in message
news:OASY$mStFHA.3452@.TK2MSFTNGP14.phx.gbl...
> Hi!
> I'm using MSDE 2000 SP4 on local computer.
> I backed up database into local hard drive using osql command like...
> OSQL -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak'" >
> LogBackupDatabase.log
> ...and it's working fine. But when I try to save backup file directly into
> network drive, it's not working. "LogBackupDatabase.log"-file contains
> error message...
> "Msg 3201, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
> Cannot open backup device 'Z:\MyFolder\MYDB.bak'. Device error or device
> off-line. See the SQL Server error log for more details.
> Msg 3013, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
> BACKUP DATABASE is terminating abnormally."
> Z: is mapped network drive.
> Is it possible to save backup file into network drive somehow? Maybe my
> osql is not correctly formed to save into backup file into network drive,
> or am I missing something?
> Also restore command using osql is not working if I try to restore backup
> file from network drive.
> --
> Thanks in advance!
> Mika
|||hi,
Mika M wrote:
in addition to Hari valuable hint, please have a look at
http://support.microsoft.com/?id=283811 in order to modify the account SQL
Server and SQL Server Agent are running on...
unfortunately this article is no longer alligned with modifications applied
with sp4.. and you will no longer be able to start and run SQL Server and
the Agent only using the reported info..
I'm still trying troubleshooting it..
try "propagating" file permissions to all sub folders as described, as long
as assigning registry permissions as
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer (FULL CONTROL)
for the account running SQL Server and
HKLM\Software\Microsoft\MSSQLServer\SQLSERVERAGENT (FULL CONTROL)
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSo cketNetLib\LastConnect
(FULL CONTROL)
HKLM\Software\Description\Microsoft\Rpc\UuidTempor aryData (FULL CONTROL)
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\ODBC\ODBC.INI (FULL CONTROL)
for the account running the Agent...
make those accounts member of the local sysadmins WinNT role
but i definitevely hope kb article 283811 get updated..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

About the location of database file.

I can install the SQL Server Express in a computer and locate the database files in another computer of the same local network?

No, you cannot.

The datafiles have to be on a local drive -NOT a mapped drive, or even an UNC share. (A properly configured NAS/SAN is different though.)

About Sql agent

Hello there,
Our sqlserver and sqlagent are started by a local administrator account, the
database backup files were stored on local disk. We are think to put databas
e
backup files to UNC directory on different server.
My question is that can we change sqlagent service to started by domain
account and let sqlserver service still run by local account? Any issue coul
d
be? Did the sqlserver and sqlagent have to run by a same account?
I really appreciate your reply,
Thanks
LionelThe one performing the backup is SQL Server, not Agent. So you need proper a
ccount for SQL Server
service. No, they don't need to use the same account. But watch out for the
mail support (SQL Mail
and Agent mail) if they don't. I prefer to have them using the same account.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:B97BAEF8-97F8-451D-9299-6843D56483E7@.microsoft.com...
> Hello there,
> Our sqlserver and sqlagent are started by a local administrator account, t
he
> database backup files were stored on local disk. We are think to put datab
ase
> backup files to UNC directory on different server.
> My question is that can we change sqlagent service to started by domain
> account and let sqlserver service still run by local account? Any issue co
uld
> be? Did the sqlserver and sqlagent have to run by a same account?
> I really appreciate your reply,
> Thanks
> Lionel
>

About Sql agent

Hello there,
Our sqlserver and sqlagent are started by a local administrator account, the
database backup files were stored on local disk. We are think to put database
backup files to UNC directory on different server.
My question is that can we change sqlagent service to started by domain
account and let sqlserver service still run by local account? Any issue could
be? Did the sqlserver and sqlagent have to run by a same account?
I really appreciate your reply,
Thanks
Lionel
The one performing the backup is SQL Server, not Agent. So you need proper account for SQL Server
service. No, they don't need to use the same account. But watch out for the mail support (SQL Mail
and Agent mail) if they don't. I prefer to have them using the same account.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:B97BAEF8-97F8-451D-9299-6843D56483E7@.microsoft.com...
> Hello there,
> Our sqlserver and sqlagent are started by a local administrator account, the
> database backup files were stored on local disk. We are think to put database
> backup files to UNC directory on different server.
> My question is that can we change sqlagent service to started by domain
> account and let sqlserver service still run by local account? Any issue could
> be? Did the sqlserver and sqlagent have to run by a same account?
> I really appreciate your reply,
> Thanks
> Lionel
>

About Sql agent

Hello there,
Our sqlserver and sqlagent are started by a local administrator account, the
database backup files were stored on local disk. We are think to put database
backup files to UNC directory on different server.
My question is that can we change sqlagent service to started by domain
account and let sqlserver service still run by local account? Any issue could
be? Did the sqlserver and sqlagent have to run by a same account?
I really appreciate your reply,
Thanks
LionelThe one performing the backup is SQL Server, not Agent. So you need proper account for SQL Server
service. No, they don't need to use the same account. But watch out for the mail support (SQL Mail
and Agent mail) if they don't. I prefer to have them using the same account.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:B97BAEF8-97F8-451D-9299-6843D56483E7@.microsoft.com...
> Hello there,
> Our sqlserver and sqlagent are started by a local administrator account, the
> database backup files were stored on local disk. We are think to put database
> backup files to UNC directory on different server.
> My question is that can we change sqlagent service to started by domain
> account and let sqlserver service still run by local account? Any issue could
> be? Did the sqlserver and sqlagent have to run by a same account?
> I really appreciate your reply,
> Thanks
> Lionel
>

About Login Account

Hi everyone,
I have a simple question for you which is too important for me.
My question is about logins in my own(local) server.
I know that my local server(my computer's server) has to default login account which are called sa and BUILTIN/ADMINISTRATOR. While I am using my own server, I do not know which default login account is used by my Server since I use windows authentication while connecting to the Server.
Another question is that while adding new login account for my own Server, I noticed that there is a choice which is comprised of Grant and Deny for the Security. What is the meaning and usage of this ?

Thanks and Regards

I suggest reading some of the security papers of Microsoft or other sites covering SQL Server Authentication, like

http://www.developer.com/tech/article.php/721441

For the beginning , sa is the sysadminstrator account (and which is a SQL Server login), Builtin/Adminstrators is a WIndows groups which is also in the role of the sysadmins by default.

You can either Grant Access to a SQL Server database or Deny access. Grant permissions are always additive, deny is restricitve, which means that if a user is in a special Windows group which is allowed to access the server but is in another group which is denied access to the server, access will be denied to him.

Jens SUessmeyer.

http://www.sqlserver2005.de

|||

Thanks for your good tutorial reference but I could not understand the following paragraph.
Would you mind explaining it to me clearly ?

With Windows Authentication we recommending placing users into NT groups and adding the NT group login to SQL Server. This powerful feature allows you to group your users together at the NT Domain level and apply permissions at the SQL Server level. For example a group named sales could exist in the NT Domain. A new login based on the NT sales group could be added to SQL Server. When a new NT account is created the NT administrator can add the new account to the NT group named sales. The SQL Server DBA can apply the appropriate role(s) and permissions to the sales group. The new NT login automatically contains the necessary permissions to log in to SQL Server because the NT sales group was previously granted a login. This strategy makes it easy for new users to be added the system.

|||

Sure,

assume the following situation. You have a production department with 5000 employees. One day you create a table storing information about timesheet. you want every employee to enter his worked time into that table. You could either grant all 5000 employees individually rights on the table to insert data or you can use the possible existing Windows group which already includes all the 500 employees and which is maintained through the active directory. So if any user will leave the company / production you don′t have to keep track as a database adminstrator of the security, because as the employee is deleted from the group he will have no longer access to the table. So you only have to do the work at a single point, the Active diretory.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Indeed, thanks very much for your explanatory reply but I wonder that how I can create a Windows group authentication.

|||Hi,

first some more to read :-) http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1061.mspx?mfr=true

If you have a domain simply create a Windows group, put user in and create a WIndows login in SQL Server naming the group. Although the creation dialog is named Windows login you can both create a Windows login from a user or a group.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks for your explanatory reply, but I have still some doubts about Windows Authentication.
Is it really possible to use Windows Authentication in order to connect an another SQL Server in my computer ? I think that we can only use Windows Authentication mode for our own local server, is it true ?

|||

Jens Suessmeyer wrote:

I suggest reading some of the security papers of Microsoft or other sites covering SQL Server Authentication, like

http://www.developer.com/tech/article.php/721441

For the beginning , sa is the sysadminstrator account (and which is a SQL Server login), Builtin/Adminstrators is a WIndows groups which is also in the role of the sysadmins by default.

You can either Grant Access to a SQL Server database or Deny access. Grant permissions are always additive, deny is restricitve, which means that if a user is in a special Windows group which is allowed to access the server but is in another group which is denied access to the server, access will be denied to him.

Jens SUessmeyer.

http://www.sqlserver2005.de

Another question is that I connect my own SQL Server with Windows Authentication mode.
When I tried to connect with SQL server Authentication mode, I could not accomplish it.
So for uor own local server, is not it allowed to connect SQL Server with SQL server Authentication mode.
Besides, As I said before, I connect my own SQL Server with Windows Authentication mode, so does this mean that I use default Builtin/Adminstrators login in order to connect ?
|||

Yes you are appearantly using Windows authentication. The mentioned group is configured by default to have access to SQL Server. If you can′t access your Server using SQL Server authentication you might not have switch the authentication mode to mixed. For that navigate in the managment Studio to the server properties, in the security tabs and switch to "Mixed Mode", you should be able to connect with any SQL Server login then (by default there is a user with the name sa (system administrator). (make sure you restart your SQL Server in order to apply the changes)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Again thanks vey much for your explanatory reply and would you mind also replying my second question since they make me very confused ?

Thanks and Best wishes

|||

Mhmm, don′t really know which question you mean but if you mean the BUILTIN\Administrators question: YOu can anser this on your own, are you in the administator group ? If yes, you are currently using that. You can have several group memberships all the permissions set in these groups are additive as long as you will get a permission set which denys anything to you.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hmm, thanks but this is not really the answer of my question.
I asked that in my local server, while I am connecting to this (my local server(my computer's server) ) , which role do I have , sa(Sytem Admistrator) or BUILTIN/ ADMISTRATOR.
And besides, I am connecting to my own local SQL server with windows Authentication so does it means that I am BUILTIN/ ADMISTRATOR ?

Thanks

|||

Jens Suessmeyer wrote:

Sure,

assume the following situation. You have a production department with 5000 employees. One day you create a table storing information about timesheet. you want every employee to enter his worked time into that table. You could either grant all 5000 employees individually rights on the table to insert data or you can use the possible existing Windows group which already includes all the 500 employees and which is maintained through the active directory. So if any user will leave the company / production you don′t have to keep track as a database adminstrator of the security, because as the employee is deleted from the group he will have no longer access to the table. So you only have to do the work at a single point, the Active diretory.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

My another question is that as I understood form your reply, firslt we should create a domain name for this Window Group. Then, we add all our corresponding users into this domain while using Windows Authentication for our group. So I ask that how can I create a group( domain name) ?

Again thanks.
Sorry for asking too much question but they really make me confused too much.
Regards|||YOu can either login using SQL Server authentication, then you have to specify a SQL Server account and apssword during the login OR you can use the Integrated security (you don′t need to provide the credentials then because it is integrated and uses your Windows context)

I don′t know in which groups you belong on your local server, but if you are using the default settings, didn′t grant any other group access to the SQL Server (and though this is your own server) I assume that you use the group memebership of BUILTIN \Administrator to authenticate against the server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Jens Suessmeyer wrote:

YOu can either login using SQL Server authentication, then you have to specify a SQL Server account and apssword during the login OR you can use the Integrated security (you don′t need to provide the credentials then because it is integrated and uses your Windows context)

I don′t know in which groups you belong on your local server, but if you are using the default settings, didn′t grant any other group access to the SQL Server (and though this is your own server) I assume that you use the group memebership of BUILTIN \Administrator to authenticate against the server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Again and again thank very much for your all extreme explanatory replies.

Now I think I kept the idea. As I understood, By default, I connect my own local server with Windows Authentication as a BUILTIN \Administrator.This group is not regarded as a Group in my opinion. Because, we can not add new user into this group. There is only one person who is the owner of the local SQL server(me) in this group. So what is the real advantage of this group ?
Meanwhile, how can I learn my sa(System administrator)'s password in order to connect my own local SQL Server as a sa(System administrator) ?

Thanks

About distributed trans

I have two Server. One is Local, another is Remote. Both Installed SQL Server 2000 at Server 2000.
I start MSDTC on both the local and the remote. The Remote SQL Server start MTS.

When I run the code forward, the error message '7391' will occur

begin distributed tran
select * from rmtdb.pubs.dbo.employee --rmtdb is a linked server
go

How to verify the OLE DB provider specified supports distributed transactions?
How can I do for this error message?
How can I reconfig the Local Server and the Remote?Make sure you have these settings before SP or code :

SET ANSI_DEFAULTS ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON

about connections and Login

Hey guys,

I have created dtsx packages and i have one master package which would run couple of child packages.

1. I have all on my local machine.I want others who r working with me access these.So what do i need to do?for them to access...

2.what would be the best way in executing these packages.

the way it is set now is i had 3 connections set for each package.

a.Source connection

b.Destination Connection.

c.One more connection(which is used with in the process of execution)

Can i set connections in common for all the packages in one location.

3.i also want to set some logging settings to my master package too.and i want this package to run everyday automatically at a particular time how can this be done.

How can i do that.

please let me know.

sureshv wrote:

1. I have all on my local machine.I want others who r working with me access these.So what do i need to do?for them to access...

What you mean, to edit them? just use VSS or place them in a shared location. To execute them, place them in a shared location and then set up a Job (SQl Agent) to run them.

sureshv wrote:

2.what would be the best way in executing these packages.

Via SQL Agent job would be my preferred method

sureshv wrote:

Can i set connections in common for all the packages in one location.

No, but you can use package configuration instead.

sureshv wrote:

3.i also want to set some logging settings to my master package too.and i want this package to run everyday automatically at a particular time how can this be done.

To schedule them, again, use a SQL Agent Job. For logging just enable package logging.