Cannot connect to database server on SQLExpress

[EvaDienske]

Hello all,

For development purposes I recently installed ArcSDE workgroup 10 on my machine, running on SQLServer Express. This worked, and I could connect to my database server fine through ArcCatalog, I had one geodatabase in this instance which I could use without issues.

Recently I added a database to the SQLExpress instance through SQLServer Management Studio, as I want to use the SQLExpress instance for other (non-SDE) databases as well. Since then (or it seems since then) I can't connect to my SQLExpress instance through Catalog anymore. It says "Cannot connect to the database server, Failed to connect to database server" when I try.

All I really did was create a new database and set some access rights on it. This should apply to the individual database and not the instance, so I don't understand why I can't use the instance over SDE anymore.

I can't work out if it's something I did through SQLServer Management Studio (something user/authentication-related), or if I'm completely on the wrong track here. Any suggestions as to what I might check or try out?

[LefterisKoumis]

Based on my discussions with ESRI tech support, you need ArcSDE Enterprise edition to connect. The workgroup edition is limited only for local connections. You can use OLE DB to access an existing geodatabase. The limitation is stated in the ESRI online documentation. When I come across the statement of limitation I will send it to you.

[EvaDienske]

No, that isn't necessarily true - but FYI, I managed to solve my own problem.

When specifying a connection to a SQLExpress database through "Database Servers" in ArcCatalog, it uses the current Windows login to make the connection (there is no way to specify a login). For some reason, it needs to validate the login name with the domain. When the error occurred, I wasn't connected to my work domain. It's the login that fails (with the above error).

This I think is strange because I can connect to the same database using SqlServer Management Studio, and this connection does not validate my login with the domain. Apparently this is something ESRI-specific?

The solution is to add a new Login to the SQLExpress instance in SqlServer Management Studio (based on SQL Server Authentication). Make sure you can log in to the instance with both Windows and SQL Server authentication. Give this user the appropriate rights.

Then specify a Database Connection in Catalog as follows:

Server: [mymachine]
Service: sde:sqlserver:[mymachine]\sqlexpress
Database: [A geodatabase in this instance]
For username and password, specify Database Authentication and the created Login.
Change the transactional version to dbo.DEFAULT if necessary.