How to set up Microsoft SQL Server to work with FirstSpot?

[Sam]

I was trying to make FirstSpot to use a MS-SQL Server 2000 database running on another machine

I created a file DSN and tested *successfully* in ODBC Manager. But when I configured FirstSpot to use the DSN file generated, and clicked on the "User" tab of Configuration Manager, it gave out the following error:

Warning:SQL error.......Login failed for user 'NT AUTHORITY\ANONYMOUS LOGIN'.,SQL state 28000 in SQLConnect in c:\program files\firstspot\cfgmgr\users.php on line 22....connection failed.

Can you give me more detailed instruction on setting up the SQL server correctly? Or is it something related to FirstSpot?

[kevin]

This is not a problem in FirstSpot. The error is due to some mis-configuration of "authentication mode" at the MS-SQL server side.

BACKGROUND INFO
==============
Microsoft SQL Server can operate one of the two security (authentication) mode:

- Windows Authentication Mode (aka Windows Authentication)
- Mixed Mode (Windows Authentication and SQL Serer Authentication)

Often Windows Authentication Mode is the default setting for a newly created server. Unless a trusted (Windows domain) connection is set up between the database server and the other machine, Mixed Mode should be chosen to make the authentication to rely purely on users defined under the SQL server (but not the NT domain).

IMPORTANT SETTINGS AT THE SQL SERVER
==============================
To make SQL Server to authenticate using mixed mode:
1. Open Enterprise Manager;
2. Expand a server group;
3. Right-click a server, and click Properties;
4. Click the Security tab;
5. Under Authentication, click SQL Server and Windows.

Also make sure you've created a valid user under MS-SQL who uses SQL authentication and has enough access rights and privileges on the firstspot user database and tables.

POINTS TO NOTE WHEN CREATING THE FILE DSN
==================================
1. When being asked "Which SQL Server do you want to connect to?",enter the server name or ip of the SQL server. In either case, please make sure you can ping that name (or address) from the FirstSpot machine;

2. When being asked "How should SQL Server verify authenticity of the login ID?", choose "With SQL Server Authentication using a login ID and password entered by the user";

3. At the same window, click on Client Configuration button

4. On the left-hand side of the window shown, choose TCP/IP as the Network libraries, click on OK to get back to the original window;

5. For the login ID, fill in the user created previously [Please make sure it has enough privilege to access your firstspot database]

After a successful testing, you can let FirstSpot to use the DSN file generated by specifying its location in FirstSpot Configuration Manager.

~ Patronsoft Limited ~

[CyberiaWiFi]

I had no big issues getting the sql to work, the only thing was finding and downloading the right MYSQL ODBC connector, which i found at mysql.com. Hope that helps someone.

Nick
_________________
www.cyberiachicago.com

[kevin]

Thanks Nick.

You're right, for connecting to MySQL database server, you need to download the MySQL ODBC driver available at their company web site.

For Microsoft SQL, the odbc driver is already bundled with all version of Windows.

~ Patronsoft Limited ~

[steveJ]

When using the dsn pointing to a MS SQL 7 database, I can create users, and they can login.

After login the Reserved 1 & 2 fields are populated but the when the user logs out, and disconnects, under the USERS tab, they are still seen online.

When the user tries to login again, they are authenticated and have the original time from the first session. They also, cannot browse as they always are redirected to the inital login page, but the infobox say's they are logged in.

I have to reset the users table in the configuration manager and Reseved1 & 2 then are NULL.

When a new user sign's up, their details are entered into the database correctly.

I have checked the dsn login permissions on the tables and they have read and write permissions to all the tables.

When using the firstspot.dsn, everything works correctly.

I can send you the dsn as the SQL is internet based.
Any idea's?

Steve

[kevin]

Hello Steve,

Your DSN file should be correct; otherwise you won't be able to create users at all.

May I know what version you're using? You can click on the "About" link in the Configuration Manager to check. Is it 2.1.1 or 2.1.2?

Version 2.1.1 had this problem with MS SQL 2000 and we've fixed it in 2.1.2 (released on Mar-5, downloadable from our site).

If you're already using 2.1.2, any chance that you can try it on SQL 2000? We can also try to get a copy of SQL 7 to test, but it may take some time for us to source that.

~ Patronsoft Limited ~

[steveJ]

I using ver 2.1.1
I'll download the newer version. and report back.

Steve

[steveJ]

Problem solved.
It must have been the same issue with SQL7 as found with SQL 2000.
ver 2.1.2 works fine on on SQL7.

Many thanks