From: Matthew Wells on
All services were running under LocalSystem. I created a new user with
Domain Admin rights and have changed all services to use this account. I
know remote connections are allowed because I've created a .udl file and
connected under sa and my windows login. I'm still getting the same
message. I also didn't see anything in the configuration manager that would
help. I connected the SSIS service to SSMS and there's nothing there
either. Do you have any other suggestions?

"JAW" <willjamu(a)mindspring.com> wrote in message
news:6ZWdnSt25eDZuaTWnZ2dnUVZ_qWdnZ2d(a)earthlink.com...
> In 2008 the account (SQL Server or Windows) if it has been granted
> DBACCESS it should have what it needs.
>
> Also, I think the access drivers come from MDAC. You should make sure that
> is on your computer as well.
>
> I have not experienced this issue in 2008.
>
>
> "Matthew Wells" <matthew.wells(a)firstbyte.net> wrote in message
> news:7K8_m.126539$Wf2.67624(a)newsfe23.iad...
>> I'm using 2008 and did a little research and found that SACM is not
>> available anymore. I found some info on configuring other SQL engines,
>> but nothing for Access mdb files. Do you know where to look?
>>
>> Thanks.
>>
>> "JAW" <willjamu(a)gmail.com> wrote in message
>> news:e98379ea-a278-476b-b78a-58b8778aaedd(a)u37g2000vbc.googlegroups.com...
>>> Assume you are running 2005 you can check the Surface Area
>>> Configuration Manager and make sure remote connections are being
>>> allowed. That is the first thing I would look at.
>>>
>>> On Dec 28, 12:50 pm, "Matthew Wells" <matthew.we...(a)firstbyte.net>
>>> wrote:
>>>> Hello
>>>>
>>>> I'm building an SSIS package from home to be deployed at an office.
>>>> The
>>>> package is a simple Access to SQL Server migration. I was building the
>>>> SSIS
>>>> package on a local machine which also housed the Access file. I
>>>> decided to
>>>> open the SSIS package from my laptop. Both the laptop and the
>>>> workstation I
>>>> was using use the same mapped share - F:\. The Access connection is
>>>> OLE DB
>>>> / Office 12. The SQL Server is an SQL Server Destination object. Now
>>>> when
>>>> I open the SQL Destination, I get this warning at the bottom and I
>>>> can't map
>>>> columns:
>>>>
>>>> "The selected data source is on a remote computer. The bulk insert
>>>> operation can be executed only on the local computer."
>>>>
>>>> When I run this package at the office, the Access file will not - and
>>>> can
>>>> not - be on the local machine. Is there a way around this?
>>>>
>>>> Thanks.
>>>>
>>>> Matthew Wells
>>>> matthew.we...(a)firstbyte.net
>>>
>
From: Erland Sommarskog on
Matthew Wells (matthew.wells(a)firstbyte.net) writes:
> All services were running under LocalSystem. I created a new user with
> Domain Admin rights and have changed all services to use this account.
> I know remote connections are allowed because I've created a .udl file
> and connected under sa and my windows login. I'm still getting the same
> message. I also didn't see anything in the configuration manager that
> would help. I connected the SSIS service to SSMS and there's nothing
> there either. Do you have any other suggestions?

You did restart the services, didn't you?`

And you did change it to a UNC path?



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Matthew Wells on
Follow up!!

I finally got it. The key seems to be that you have to open port 1434 for
SQL Server Browser. That's pretty much all I did. I didn't even open up
ports on the local machine firewall.

BTW, you can find Surface Area Configuration Manager in SSMS. Right click
the server and click Facets. SACM is in the drop down at the top. Doesn't
seem necessary since all the options can be located elsewhere.

Thank you all for your replies.


"Matthew Wells" <matthew.wells(a)firstbyte.net> wrote in message
news:sjp_m.12261$yM1.2703(a)newsfe11.iad...
> All services were running under LocalSystem. I created a new user with
> Domain Admin rights and have changed all services to use this account. I
> know remote connections are allowed because I've created a .udl file and
> connected under sa and my windows login. I'm still getting the same
> message. I also didn't see anything in the configuration manager that
> would help. I connected the SSIS service to SSMS and there's nothing
> there either. Do you have any other suggestions?
>
> "JAW" <willjamu(a)mindspring.com> wrote in message
> news:6ZWdnSt25eDZuaTWnZ2dnUVZ_qWdnZ2d(a)earthlink.com...
>> In 2008 the account (SQL Server or Windows) if it has been granted
>> DBACCESS it should have what it needs.
>>
>> Also, I think the access drivers come from MDAC. You should make sure
>> that is on your computer as well.
>>
>> I have not experienced this issue in 2008.
>>
>>
>> "Matthew Wells" <matthew.wells(a)firstbyte.net> wrote in message
>> news:7K8_m.126539$Wf2.67624(a)newsfe23.iad...
>>> I'm using 2008 and did a little research and found that SACM is not
>>> available anymore. I found some info on configuring other SQL engines,
>>> but nothing for Access mdb files. Do you know where to look?
>>>
>>> Thanks.
>>>
>>> "JAW" <willjamu(a)gmail.com> wrote in message
>>> news:e98379ea-a278-476b-b78a-58b8778aaedd(a)u37g2000vbc.googlegroups.com...
>>>> Assume you are running 2005 you can check the Surface Area
>>>> Configuration Manager and make sure remote connections are being
>>>> allowed. That is the first thing I would look at.
>>>>
>>>> On Dec 28, 12:50 pm, "Matthew Wells" <matthew.we...(a)firstbyte.net>
>>>> wrote:
>>>>> Hello
>>>>>
>>>>> I'm building an SSIS package from home to be deployed at an
>>>>> office. The
>>>>> package is a simple Access to SQL Server migration. I was building
>>>>> the SSIS
>>>>> package on a local machine which also housed the Access file. I
>>>>> decided to
>>>>> open the SSIS package from my laptop. Both the laptop and the
>>>>> workstation I
>>>>> was using use the same mapped share - F:\. The Access connection is
>>>>> OLE DB
>>>>> / Office 12. The SQL Server is an SQL Server Destination object. Now
>>>>> when
>>>>> I open the SQL Destination, I get this warning at the bottom and I
>>>>> can't map
>>>>> columns:
>>>>>
>>>>> "The selected data source is on a remote computer. The bulk insert
>>>>> operation can be executed only on the local computer."
>>>>>
>>>>> When I run this package at the office, the Access file will not - and
>>>>> can
>>>>> not - be on the local machine. Is there a way around this?
>>>>>
>>>>> Thanks.
>>>>>
>>>>> Matthew Wells
>>>>> matthew.we...(a)firstbyte.net
>>>>
>>