SQL SERVER Firewall test

William Burke 1 Reputation point
2021-09-02T14:31:08.267+00:00

I have a SSIS script that validates directory's and files exists before the SA job continues. This task works on my local 2016 server, and our old 2008 production server but fails on our new 2019 server, which is a virtual server we lease through UNISYS. I believe this is a firewall issue. I am looking for a way to test the to see if this is a firewall issue. I came up with the TSQL script below but it always returns "No". Even when I run it on my local server or from our old server. I know they have assess. Why does this not work and how do find out why the new server running SSIS package returns Directory does not exist error. Thanks for any help in advance. 

Declare @isExists INT  
EXEC xp_fileExist '\\Domain\apps\GMSIMBTracing\History\IMB_VSAM_FILE.TXT' ,@isExists OUTPUT  
--Print @isExists  
SELECT CASE @isExists   
WHEN 1 THEN 'Yes' ELSE 'No' END as isExists
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,814 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 21,511 Reputation points
    2021-09-02T14:38:56.483+00:00

    Hi @William Burke ,

    SQL Server 2017 introduced few helpful DMVs for such tasks. They are much better than older extended system stored procedures: xp_...

    SQL 

    -- SQL Server 2017 onwards dmv  
SELECT file_or_directory_name  
   , level, is_directory, creation_time, size_in_bytes  
FROM sys.dm_os_enumerate_filesystem  
(  
   N'\\Domain\apps\GMSIMBTracing\History'  
   , N'*.txt'  
);
    0 comments
  2. Erland Sommarskog 78,261 Reputation points MVP
    2021-09-02T21:27:00.41+00:00

    I am not sure how that could be a firewall test. You are trying to access a share on a different computer, and this can be problematic, if SQL Server runs as NT Service\MSSQLSERVER or a similar machine-local account.

    But I can't really see how this can be related to access problems you have from SSIS.

    0 comments
  3. AmeliaGu-MSFT 13,901 Reputation points Microsoft Vendor
    2021-09-03T05:44:01.113+00:00

    Hi WilliamBurke-1905,
    Welcome to Microsoft Q&A.
    For Firewall, please make sure Port 135 has been opened. Please refer to Configure a Windows Firewall for Access to the SSIS Service.

    why the new server running SSIS package returns Directory does not exist error.

    Please ensure that the account running SSIS package has permissions to access all folders and files in the path you are accessing. Please refer to this similar thread which might be helpful.
    Best Regards,
    Amelia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments