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.


CASE 1:

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.


CASE 2:

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 Later

   SQL 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:

Allow Network Connections

Allow Network Connections

 

Now  to  address  the  sa  connection  issues.

Scroll down in SQLStudio to the Security section, and expand the tree to see the sa user.

sa logon properties

sa logon properties

 

In  the  properties  for  sa  user,  change  the  Status  from  Disabled  to  Enabled.

sa Logon Enable

Enable here.

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   .

SQL User Logon Setting

User Logon Setting

 

 

 


 

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.

 

John  Griffiths