SQL Server Connection Problems

[GD_Monkey]

I'm using EA Corporate Edition v7.1 (Build 834).
The model exists on a remote SQL Server which A.N. Other person can connect to. However, I cannot.
I have been into Admin Tools->ODBC and set up the System DSN and tested connectivity - all fine.

When I start EA and click on Connect to Server... on the start page the mouse pointer briefly changes to something else then back to an arrow but no dialog box appears. If I manually enter the connection string in File->Open Project... I get an error dialog with a title of EA and containing an exclamation and "[-2147467261]".

Any ideas what could be wrong?

Regards,

Monkey

[«Midnight»]

This sounds like a SQL Server privilege problem, not a system access one. You seem to be able to reach the server just fine, which is why you can successfully test the connection.

If SQL Server does not recognize your account - complete with network credentials (machine or domain name as well as user name) - then you will fail if you try to access anything. Even if SQL Server does recognize you, or grants you default privileges as a guest, you could receive failures when EA tries to open tables for read-write access (since this level of access is unlikely to be granted to guests as a default).

You or your DBA need to log onto SQL Server with an administrator account, then add your Windows user account to the recognized login names for your database. If you don't do so when you create the login entry, add roles to the new login so that you will have write access to the EA model.

HTH, David

[smendonc]

I may have missed something in the original question, but if you don't get a 'Data Link Properties' dialog box when you click on 'Connect to Server...' from the start page then you can't assume it's a SQL Server issue, as EA hasn't even been configured at this point to know that it would connect to a SQL Server.

Something to try when the data string is entered directly through File-->Open Project, make sure the little 'Connect to Server' box is checked.  I don't know if it makes a difference if the string is obviously a DSN, but it is always checked for Server access and never for eap files.

Also I've never explicitly configured an ODBC connection from Admin-->Tools to connect to a SQL Server database, I've always used the OLE DB provider from the pop up box; example connect string below.

SparxTest --- DBType=1;Connect=Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SparxTest;Data Source=STANSPC\SPARKSQLEXPRESS

The error number you are getting appears to get thrown up when EA thinks the connect string is not valid.  There may be other reasons as well.

Regards,
Stan.

[GD_Monkey]

Thanks for the feedback guys.
I always do tick the "Connect to Server" box and my connection string is:
<project> on SQL Server --- DBType=1;Connect=Provider=SQLOLEDB.1;Password=<password>;Persist Security Info=True;User ID=<user_ID>;Initial Catalog=<catalog>;Data Source=<server name>

As I said, one of my colleagues can access the model using the exact same connection string (including the same user name and password). If we're using the user/password access rather than Windows account authentication does this rule out a domain/user access privelege problem?

[«Midnight»]

I don't know...

First, remember that what I am pointing to might not be the problem at all. It is just something I have run into, and it can result in the same kind of behavior - you can create valid connections to SQL Server, but whatever you try to do just fails. This kind of thing is not isolated to EA. EA and other highly automated clients sometimes report miscellaneous error codes. SQL Server will often report (one of a set of) generic catch-all error codes to client applications that are not fully trusted.

The SQL Server documentation is not too clear on this. [Actually, it is probably clear from Microsoft's point of view. If you are already familiar with the problem and context it might make perfect sense, but for someone who's encountering it for the first time - you are unlikely to troubleshoot this twice - it can seem a bit vague. Sadly, that's not the voice of wisdom, it is experience.]

The point I am making is that if this is your problem, then it has to be solved at the SQL Server end. (AFAIK) It is not something you can do from the client end.

David

[GD_Monkey]

I've just downloaded an app from t'interweb which brings up the Data Link Properties dialog (the one that EA shows when you click on Connect to Server... on the Start Page) and whilst that works on one machine it doesn't work on the one where the problem currently exists with EA. This *must* be down to missing/incompatible system DLLs and/or registry entries or similar configuration.
Now all I have to do is work out what it is that's causing it not to be displayed....

[«Midnight»]

I still think your problem is on the server side. If you can connect at all then go to the server - as in log in there, or with a SQL Server administrator account for the SQL Server instance you are trying to connect to. I do not mean connect through EA; connect through Windows as if you were going to maintain one of the databases (which is what you are going to do).

Open up the database your model lives in, look in the Security section and edit users (logins) to add your account complete with domain or machine ID via the Search (or Find) option. If the account is already there - once again, with the domain prefix - then go to the next step.

Edit the user by assigning roles that have the necessary access to modify the EA model. This is not the default setting for a new SQL Server user entry, so you need to add a role that has access.

Now try to connect. Any luck?

[GD_Monkey]

Okay, I've managed to fix the problem by running through the steps here (http://www.macropool.com/en/download/mdac_xp_sp2.html) to fix MDAC.
Apparently...

Windows XP with Service Pack 2 contains MDAC version 2.81, which is currently not available for download at the Microsoft Web site as a standalone installation. If you try to install MDAC 2.8, which is the latest available standalone download of MDAC, you will receive a message that you already have a newer version of MDAC installed.

[«Midnight»]

The main thing is that you are moving forwards again.

Thanks for closing the loop for the rest of us!

David