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!
How 2 Configure Site Authentication to use Microsoft SQL DB
-
- Posts: 15
- Joined: Tue Dec 18, 2018 4:21 pm
- Location: Newark, NY
- Contact:
-
- Site Admin
- Posts: 2080
- Joined: Tue Sep 29, 2009 6:09 am
Re: How 2 Configure Site Authentication to use Microsoft SQL
You can use ODBC database for storing user accounts.
-
- Posts: 15
- Joined: Tue Dec 18, 2018 4:21 pm
- Location: Newark, NY
- Contact:
Re: How 2 Configure Site Authentication to use Microsoft SQL
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).FTP wrote:You can use ODBC database for storing user accounts.
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?
-
- Site Admin
- Posts: 2080
- Joined: Tue Sep 29, 2009 6:09 am
Re: How 2 Configure Site Authentication to use Microsoft SQL
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
And you can also have a look on this KB article: viewtopic.php?f=5&t=272
-
- Posts: 15
- Joined: Tue Dec 18, 2018 4:21 pm
- Location: Newark, NY
- Contact:
Re: How 2 Configure Site Authentication to use Microsoft SQL
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!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
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?
-
- Site Admin
- Posts: 2080
- Joined: Tue Sep 29, 2009 6:09 am
Re: How 2 Configure Site Authentication to use Microsoft SQL
Maybe you can create a new domain which using database, and then import user accounts like this:
viewtopic.php?f=6&t=295
viewtopic.php?f=6&t=295