MSSQL Clarion Connection Solving
Migrating from older MSSQL Servers
Windows 8, 8.1 and MSSQL2008 and 2012
Recently I have been involved in moving some older databases from MSSQL 2005 to MSSQL 2008. In both cases the driving force was the ageing servers necessitating a change in the server hardware, and along with that, the newer version of MSSQL.
This report should help you WHEN you need to do the same.
New server hardware, running SBS2010 and fresh MSSQL2008 Express Instance.
Once installed, and rebooted, we tried to connect using MSSQLStudio using the sa user and correct password. We could not connect to the SQLServer, not even when using the keyboard at the server. At this stage, the server was stand alone and not connected to any other PCs. Next step was to try connecting as a Windows Authenticated User. Well that worked.
We checked that we had set the SQLServer to used both Windows Authentication and MSSQL User Authentication, and all that looked OK.
We checked that the SQLServer was set to allow network connections, and yes, that was set.
Once we did a restore from backup onto the new SQL Instance, we hooked up one of the existing PCs to the network and tried running the Clarion built program. Yep, you guessed it, lots of Connection Failed messages.
We then had the bright idea of installing MSSQL Studio onto the client PC and trying to connect from there, again with the sa user and sa password. SNAFU. Could not connect (even with all firewalls disabled).
OK, Windows Authentication was working on the server, so we resolved to create script to set up the client PC user as a SQL user on the server, and give this user ownership of the database we need.
Here is the script we ran on the Server
USE MASTER GO CREATE LOGIN [ADVANCEMAN\FREDDIE] FROM WINDOWS WITH DEFAULT_DATABASE=LMADVANCEMAN GO -- Optionally, go all the way, add Freddie as a sysadmin for now -- to be deprecated - dont use: EXEC sp_addsrvrolemember 'ADVANCEMAN\FREDDIE', 'sysadmin' ALTER SERVER ROLE sysadmin ADD MEMBER [ADVANCEMAN\FREDDIE] GO -- Now add to the database role of db_owner USE LMADVANCEMAN GO -- to be deprecated EXEC sp_addrolemember 'db_owner', 'demo' ALTER ROLE db_owner ADD MEMBER [ADVANCEMAN\FREDDIE] GO -- Adding connect permission for windows user ADVANCEMAN\FREDDIE -- is now not needed as we made him an owner.... -- but here is the example -- USE LMADVANCEMAN -- GO -- GRANT CONNECT TO [ADVANCEMAN\FREDDIE] -- GO
Problem solved. We could now connect from our program using TrustedConnection, and SQLStudio express also connected this way. But still no way to connect using the sa and SQL Authentication. That would have to wait for the resolution in Case 2 below.
This episode was because I was setting up a new Windows 8.1 laptop for testing.
I quickly discovered that you cannot install MSSQL 2005 on Windows 8/8.1.
That €™s OK, so I went with SQL 2008. This was done several months ago, and I recall having some connection problems with that.
Recently I tried again, with SQL2012. I tried connecting with the new sa user and sa password and I could not connect.
I had to use the WindowsAuthentication. But I want/need the Clarion application to connect using SQL Authentication. And I want to be able, in some cases, to connect from a Clarion application using the sa connection (you know, for adding a table/field to the database).
Brick walls everywhere. Nothing would work.
I started thinking about the trusty SQL Configuration Manager from SQL2005. Hmm, could not find it, and could not find anyway to install it. Turns out, MS have moved it from being an EXE you run, to a snap-in .
My search turned up this gem:
To access SQL Server Configuration Manager Using Windows 8 and LaterSQL Server Configuration Manager is now a snap-in for the Microsoft Management Console program and not a stand-alone program, SQL Server Configuration Manager does not appear as an application when running Windows 8. To open SQL Server Configuration Manager, in the Search charm, under Apps, type SQLServerManager11.msc (for SQL Server 2012) or SQLServerManager10.msc for (SQL Server 2008), and then press Enter.
It all looked OK, but the SQL Browser service was not enabled and thus not running. By default, this is set disabled on new installations for security reasons. I fixed, that, set it to Automatic and Started it. I also restarted the SQL Server just in case.
Still, I could not connect using the sa user, or my program.
Further research and I discovered these facts:
1) Upon installation SQL Server 2008 and 2012, the sa user is disabled
2) All password use is set to Strict
This is why I was getting nowhere, with this Case 2 and Case 1 above.
Now to solve the issues
First, connect using SQLStudio using Trusted Connection, select the server instance, and go to properties.
Check that remote connections are enabled:
Now to address the sa connection issues.
Scroll down in SQLStudio to the Security section, and expand the tree to see the sa user.
In the properties for sa user, change the Status from Disabled to Enabled.
Now the sa user should be able to connect.
Secondly, I have a SQL 2000 and SQL 2005 user set up at several sites where the user set for connection to the database does not not have a strict password set. In one case it is something like plumB#42. And that is not strong enough for the new server instances.
So, we need to change the password to something stronger that SQL2008/2012 will be happy with, or we can turn off the strict password requirement for this one user. Here is the process:
Rt-Click that user in the security section as shown above, and in the General section, uncheck the option
[ ] Enforce password policy .
The above resolutions are the easiest way to resolve the issues where you have databases out in the real world that will eventually be migrated from older SQLServers. Doing things as outlined will weaken the security of the SQL systems, but may prevent you having to make a lot of time consuming changes to existing setups.
For all new work, it is strongly recommended that you play the game the way Microsoft is trying to direct you, that is, to use only Windows Authentication.