ColdFusion can't add new datasource.

[moriarty4839]

Windows Server 2019, IIS running.  Database engine is SQL Server Express 2019, Cold Fusion 2021.

I created a new database (Westfield_New) using SQL Server Management Studio, using Administrator (owner) and the password.  Can add tables, etc from SSMS, so database and connection good.

I now try to add a new datasource from ColdFusion Administrator.  Data Source Name = Westfield, Driver = Microsoft SQL Server, click 'Add'.

CF Data Source Name = Westfield, Database=Westfield_New(what it's called in SQL Server),

Server=WindowsServer, Port=1433, User Name=Administrator(owner of the database in SQL Server), Password=[Administrator password], Description blank.  Click 'Submit'.

I get the following error message;

"Connection verification failed for data source: Westfield
java.sql.SQLInvalidAuthorizationSpecException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'Administrator'."

Any ideas, as I'm using the same credentials to access the database from CF as I am from SSMS?

Thanks in advance

Roy

[BKBK]

It is clear that ColdFusion is not allowed to connect to the database. To enable connection, there are several checks you have to do first. For example,

1. Make sure when you start MS SQL Management Studio that the authentication method is 'SQL Server Authentication';
2. Make sure in SQL Server Configuration Manager that TCP/IP is enabled for all protocols; 
3. Replace Server=WindowsServer with Server={IP_ADDRESS_OF_DATABASE_SERVER}. In my case, this is Server=127.0.0.1.

Remember to restart ColdFusion after any changes.

If you still don't get a connection, then follow the steps of someone else who solved a similar problem.