How 2 Configure Site Authentication to use Microsoft SQL DB

Please post here if you have problems in using Wing FTP Server.
Post Reply
jhoag
Posts: 15
Joined: Tue Dec 18, 2018 4:21 pm
Location: Newark, NY
Contact:

How 2 Configure Site Authentication to use Microsoft SQL DB

Post by jhoag »

I tried to find the answer for this generally online by googling "Does MySQL and Microsoft SQL work interchangeably", but based on that search it doesn't appear they work in the same way. My organization uses Microsoft SQL and I can quickly and easily create an SQL database for WingFTP to use when storing usernames and passwords.

So my question is, can a WingFTP site be configured to connect to a Microsoft SQL server Database using the existing MySQL Settings tab that appears on the Site Authentication settings page, or can only strictly MySQL be used?

If it's possible to use a Microsoft SQL Database for authentication, is there a utility or feature that reads what is already stored in XML files, and converts them or imports data from each into the Microsoft SQL Database? Or would we need to manually create new accounts and passwords if we converted a site from using XML Files for authentication to the Microsoft SQL database for authentication?

Hope my need makes sense. Thanks for any help you can give!
FTP
Site Admin
Posts: 2072
Joined: Tue Sep 29, 2009 6:09 am

Re: How 2 Configure Site Authentication to use Microsoft SQL

Post by FTP »

You can use ODBC database for storing user accounts.
jhoag
Posts: 15
Joined: Tue Dec 18, 2018 4:21 pm
Location: Newark, NY
Contact:

Re: How 2 Configure Site Authentication to use Microsoft SQL

Post by jhoag »

FTP wrote:You can use ODBC database for storing user accounts.
I actually tried to do that, but there doesn't seem to be any specifics on what driver needs to be used or how exactly the ODBC data source needs to be configured. So for example, I setup a SYSTEM DSN using the ODBC Data Source Administration module on the Wing FTP server (runs Windows Server 2008), to connect to an SQL 2014 database using the SQL Server driver (version 6.01.7601.17514).

The SQL Server is on another box and I can ping it from the Wing FTP server. Also when I TEST the connection using the System DSN that's setup on the Wing FTP server, I get the following:

==================================================
Microsoft SQL Server ODBC Driver Version 06.01.7601

Running connectivity tests...

Attempting connection
Connection established
Verifying option settings
Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!
==================================================

When I try to plug in the "Data Source Name" and the SQL username/password for the data source into the "ODBC Settings" tab of the "Authentication Settings" module in the Wing FTP server settings for my site, all I ever seem to get is: Database connection failed!

I can connect to my data source using another method, such as Microsoft Access with no issue. Suggestions?
FTP
Site Admin
Posts: 2072
Joined: Tue Sep 29, 2009 6:09 am

Re: How 2 Configure Site Authentication to use Microsoft SQL

Post by FTP »

I think you need to input the database user/pass under Windows ODBC Manager, not WingFTP's settings.
And you can also have a look on this KB article: viewtopic.php?f=5&t=272
jhoag
Posts: 15
Joined: Tue Dec 18, 2018 4:21 pm
Location: Newark, NY
Contact:

Re: How 2 Configure Site Authentication to use Microsoft SQL

Post by jhoag »

FTP wrote:I think you need to input the database user/pass under Windows ODBC Manager, not WingFTP's settings.
And you can also have a look on this KB article: viewtopic.php?f=5&t=272
Awesome! Yes the key in my situation is using the Windows 32 Bit ODBC Data Source Administrator instead (c:\windows\sysWOW64\odbcad32.exe) of the 64 bit which is the default "icon" that Windows has on it's start menu, and now WingFTP can talk to the connection. That's great!

Now what about the XML files for users? Is there a way to convert those or import those into SQL so that I don't have to recreate all of the accounts we have in XML format, to SQL records?

Wouldn't happen to have a snappy utility to convert the XML FTP User records to a CSV file would you?
FTP
Site Admin
Posts: 2072
Joined: Tue Sep 29, 2009 6:09 am

Re: How 2 Configure Site Authentication to use Microsoft SQL

Post by FTP »

Maybe you can create a new domain which using database, and then import user accounts like this:
viewtopic.php?f=6&t=295
Post Reply