SQL Server connection conflict

[oxie18]

Hi everyone!

A client is having a problem connecting to SQL Server from Accpac v62. It's in conflict with their MIS reports connection. They have setup 2 System DSNs (C:\Windows\SysWOW64\odbcad32.exe), 1 for the MIS reports and 1 for Accpac. MIS and Accpac are connecting to 2 different SQL servers. Both connects successfully when testing the connection in odbcad32.

MIS DSN: <server1>32
SQL Server: <server2>

Accpac DSN: Sage300
SQL Server: <server1>

MIS reports can connect successfully to server2 but when we changed the database profile for the Accpac company in Database Setup to connect through "Sage300" DSN given the same user name and password used in odbcad32.exe, it gives a login failed error. Previously it is using the server name in the database profile. If we delete the DSN for MIS in odbcad32 and set the database profile for the Accpac company to connect via the server name, you can connect to Accpac but then they can't connect the MIS reports to server2.

What might it be? I'm suspecting the DSN name used for the MIS reports? Please help. Thanks in advance.

 

[Jay Converse]

For Accpac, never set up separate DSNs, just use the server name in Database setup.

The rest of your problems are simply because your credentials are incorrect.

 

[oxie18]

Found a solution for this: modified all Accpac company profiles in Database Setup to use Sage300 DSN instead of the server name. Found out that Accpac automatically creates the DSN <servername>32 if company profile is set to server name in database setup.

 

[oxie18]

Hi tuba,

Client can't rename the DSN used for their MIS reports because they have to modify and rebuild all their reports to use a new DSN. And they have tons so they opted to create a new DSN for Accpac and just setup the DSN on each workstation that's using Accpac.

 

[Jay Converse]

Then there's your problem, IT shouldn't have used the <servername>32 DSN in the first place, that's reserved for Accpac. It needs to have SQL credentials, and not set a default database.

 

[oxie18]

Agreed, exactly my point.