question

WilliamBurke-1905 avatar image
0 Votes"
WilliamBurke-1905 asked AmeliaGu-msft commented

SQL SERVER Firewall test

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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi WilliamBurke-1905,

How are things going?
Do the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered

Hi @WilliamBurke-1905,

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'
 );
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.