6

In the past I have used xp_cmdshell in SQL Server to invoke BCP utility for importing a text file into a table on SQL Server between two Windows machines. The two machines were on the same domain without needing a password (since the SQL server command uses a trusted/password less connection). This was done by setting the directory of the source machine to make files inherit group ownership by which the destination machine was a part of.

I would like to know if this would also be possible when using the same approach for importing a text file from a Solaris Server to the same Windows Server but on a different domain.

The following is the stored procedure 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'm getting a SQL Native Error 0 since the connection cannot be made.

I'm wondering if because last time the machines were on the same domain, I only needed to put the directory "\ftpprod\testserver\file.txt" and this time the domains are different I have to use "ftp:\10.251.11.10\TestFile.txt", that this will make it impossible.

Angel Cloudwalker
  • 2,015
  • 5
  • 32
  • 54
  • 2
    We can't be expected to know how permissions are set up in your new environment. It might blow up, or ... it might work. You should try using your code. If it works, please delete this Q. If it doesn't work, then update your Q with error messages that you get. Good luck. – shellter Dec 21 '15 at 18:28
  • The error is SQLState = S1000, NativeError = 0. Basically SQL Server cannot connect to the server that uses Solaris. What I'm asking for is what you are asking me, using SFTP how can I set up permissions that uses the -T (Trusted) password less connection. What are some options to set up these permissions. – Angel Cloudwalker Dec 23 '15 at 21:28
  • 1
    Have you tried using telnet or an ftp client from FileZilla for example to test both the connection and the permissions. Once you can do it manually, it should be more straightforward to do it prrogramatically – Peter Smith Dec 23 '15 at 21:49
  • I don't have access to an environment that mirrors your problem. Also rather than continue discussions in comments, you should be rewriting your Q to make something that people can test against. Sorry, but pluse-uno for posting a bounty, you should get some help. Good luck. – shellter Dec 23 '15 at 21:51
  • The admins can connect, there is a username password they set up for the machine to gain access. The issue is I want to use xp_cmdshell which is password less as you can see in the code I supplied. If this is not possible how can I in a stored procedure access that text file on the Solaris server? – Angel Cloudwalker Dec 23 '15 at 21:52
  • @shellter The code is completely testable, the bcp command just needs to be altered with your specific destination and sources. But the bigger issue I suspect are the permissions or perhaps even using a completely different logic for the stored procedure. – Angel Cloudwalker Dec 23 '15 at 22:02
  • BCP isn't going to be able to connect to that file directly. You'll have to pull the file to a store that is accessible via UNC path. So this is probably a two step process of SFTP data then Load. both processes are possible via XP Command shell, you'll just have to Call something like the command line interface for WinSCP to do the transfer. – Brad D Dec 30 '15 at 19:28
  • @ACsteel please update your question to include all the details that you have mentioned in comments into the question itself – Mauricio Gracia Gutierrez Dec 31 '15 at 11:48
  • @MauricioGracia I have updated the question, thanks. – Angel Cloudwalker Dec 31 '15 at 15:00

4 Answers4

0

It is possible, simply use psftp to automate the ftp part, then on a second step call bcp to import from the local directory.

Eduardo
  • 7,631
  • 2
  • 30
  • 31
  • Using this method I have to create a batch file that is ran by the Windows server correct? I would like a solution that is handled entirely in a SQL stored procedure. – Angel Cloudwalker Dec 23 '15 at 21:57
  • Well, but you won't be able to connect to an sftp server from SQL server anyway. If you have enough admin rights on the SQL Server, you could write an extended stored procedure in C# to do whatever you want in the nicest way possible. Although that would still require installing the dll. – Eduardo Dec 23 '15 at 23:15
  • Is it because it's a Solaris machine? Because I have been able to connect via SFTP to a Windows machine. – Angel Cloudwalker Dec 24 '15 at 12:14
  • Using the same command in my question except I didn't put an IP address since they were on the same domain: 'bcp [Destination].dbo.AC_Test in "\\ftpprod\osrtrans\TestFile.txt" -c -F2 -r\n -t^| -Ssqldevelopment\sqldevelopment -T'; – Angel Cloudwalker Dec 31 '15 at 15:17
0

It is best that you go for @Eduardo's response. you are going to face lots of issues what the xp_cmdshell,

  1. Who is going to call it?

  2. What security context will it run under.

  3. Is there any other transactions which you are going to use along with it MSDTC stuff.

  4. you will have to mirror all of this in dev/uat/production.

    hope this helps.

Community
  • 1
  • 1
bhushanvinay
  • 449
  • 1
  • 5
  • 21
0

I might not be understanding the question correctly, but ftp is a standard network protocol, so as long as the settings and permissions are set up properly, the host OS shouldn't make any difference to the functionality of the ftp server itself.

ie: if it worked on a ftp server hosted on a windows box, it'll do the same thing if the ftp server is hosted on a unix box assuming all settings are the same.

C.Chen
  • 69
  • 5
0

Seems that you are asking if it is possible and how to change/setup the permissions on the Solaris machine.

The steps provided here might change depending on your Solaris version and specific settings that your environment have.

This steps are provided without any guarantee and you should never follow security advices or steps "blindly" on how to setup the security of a system that you are responsible for, just because you found the answer online (even in stack overflow), make sure that you understand and test every single line shown here before applying this to a production system

On the server side,depending on the Solaris version that you have, you need to following steps.

This procedure configures an sftponly directory that is created specifically for sftp transfers. Users cannot see any files or directories outside the transfer directory.

All the following steps are executed with the root role. .

On the Secure Shell server, create the 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 write permission to the sftponly/WWW subdirectory.

Still on the server, configure a match block for the sftp group.

In the /etc/ssh/sshd_config file, locate 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

You can use the following variables to specify the chroot path:

%h – Specifies the home directory.

%u – Specifies the username of the authenticated user.

%% – Escapes the % sign.

On the client, verify that the configuration works correctly.

The files in your chroot environment might be different.

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

Mauricio Gracia Gutierrez
  • 10,288
  • 6
  • 68
  • 99