Geek Answers Handbook

SQL Server executes xp_cmdshell command to import a text file via FTP from a UNIX window

In the past, I used xp_cmdshell in SQL Server to invoke the BCP utility to import a text file into a table on SQL Server between two Windows machines. These two machines were in the same domain without a password (since the SQL server command uses a stronger / password connection less). This was done by setting the source computer directory so that the files inherit the group ownership with which the destination machine was part.

I would like to know if this would also be possible using the same approach to import a text file from a Solaris server to the same Windows Server, but in a different domain.

The following is the stored procedure that I would like to execute:

DECLARE @CMD VARCHAR(6000), @SystemCode INT; DECLARE @ResultsFromCommand TABLE(SystemMessage VARCHAR(500)); SELECT @Cmd ='bcp [Destination].dbo.AC_Test in "ftp://10.251.11.10/TestFile.txt" -c -F2 -r\n -t^| -Ssqldevelopment\sqldevelopment -T'; INSERT @ResultsFromCommand EXEC @SystemCode = MASTER.dbo.xp_cmdshell @Cmd; SELECT * FROM @ResultsFromCommand;

I get SQL Native Error 0 because the connection could not be completed.

I’m curious that since the last time the machines were in the same domain, I only needed to put the directory "\ ftpprod \ testserver \ file.txt", and this time the domains are different. I need to use "ftp: \ 10.251.11.10 \ TestFile.txt" to make this impossible.

+6
source share
4 answers

Maybe just use psftp to automate the ftp part, then in the second step bcp to import from the local directory.

0
source

Your best bet is for @Eduardo's answer. you will encounter many problems that xp_cmdshell,

  • Who will call it?
  • In what security context will it work.
  • Are there any other transactions that you intend to use with MSDTC materials?

  • you will have to reflect all this in dev / uat / production.

    hope this helps.

0
source

Perhaps I misunderstood the question, but ftp is the standard network protocol, so if the settings and permissions are configured correctly, the host OS should not have any significance for the functionality of the ftp server itself.

i.e.: if it was running on an ftp server hosted in a Windows window, it would do the same if the ftp server hosted in a unix block, provided that all settings are the same.

0
source

It looks like you are asking if this is possible and how to change / configure permissions on a Solaris machine.

The steps shown here may vary depending on your version of Solaris and the specific options that your environment has.

These steps are provided without any guarantees, and you should never follow the security tips or the steps “blindly” on how to configure the security of the system for which you are responsible, just because you found the answer online (even in a stack overflow), make sure you understand and test every single line shown here before applying it to the production system

On the server side, depending on your version of Solaris, you need to follow these steps.

This procedure sets up the sftponly directory, created specifically for sftp submission . Users cannot see files or directories outside the transfer directory.

All actions are performed with the root role ..

On the Secure Shell server, create an isolated directory as a chroot environment.

 # groupadd sftp # useradd -m -G sftp -s /bin/false sftponly # chown root:root /export/home/sftponly # mkdir /export/home/sftponly/WWW # chown sftponly:staff /export/home/sftponly/WWW

In this configuration, / export / home / sftonly is the chroot directory that only the root account has access to. The user has permission to write to the sftponly / www subdirectory.

On the server, configure the matching block for the sftp group.

In the / etc / ssh / sshd _config file, find the sftp subsystem entry and modify the file as follows:

 # pfedit /etc/ssh/sshd_config ... # sftp subsystem #Subsystem sftp /usr/lib/ssh/sftp-server Subsystem sftp internal-sftp ... ## Match Group for Subsystem ## At end of file, to follow all global options Match Group sftp ChrootDirectory %h ForceCommand internal-sftp AllowTcpForwarding no

The following variables can be used to indicate the chroot path:

% h - Specifies the home directory.

% u - Specifies the username of the authenticated user.

%% - Resets the% sign.

In the client, verify that the configuration works correctly.

Files in your chroot environment may vary.

 root@client :~# ssh sftponly@server This service allows sftp connections only. Connection to server closed. No shell access, sftp is enforced. root@client :~# sftp sftponly@server sftp> pwd sftp access granted Remote working directory: /chroot directory looks like root directory sftp> ls WWW local.cshrc local.login local.profile sftp> get local.cshrc Fetching /local.cshrc to local.cshrc /local.cshrc 100% 166 0.2KB/s 00:00user can read contents sftp> put /etc/motd Uploading /etc/motd to /motd Couldn't get handle: Permission denieduser cannot write to / directory sftp> cd WWW sftp> put /etc/motd Uploading /etc/motd to /WWW/motd /etc/motd 100% 118 0.1KB/s 00:00user can write to WWW directory sftp> ls -l -rw-r--r-- 1 101 10 118 Jul 20 09:07 motdsuccessful transfer sftp>

This was taken from this document.

https://docs.oracle.com/cd/E36784_01/html/E37125/sshuser-18.html

0
source

More articles:


All Articles