I am ready to give up with this product. It's making me feel like a complete idiot that I can't connect to the server. It's on the same machine, the service name is MSSQLSERVER, the password for the sa account is correct, I don't know what else to try. Does it really need to be this difficult?
You will have to uninstall and re-install SQL Server 2008. You want to install using the SQL Server and Windows Authentication (Mixed) Mode. Remember the new instance name and your password. Attached is a MS doc explaining the authentication. Make sure you have the Spatial dll's from OSGeo for SQL Server Spatial 2008.
It looks like the issue was the service name. If anyone out there works in the UI or User Experience department, I would seriously consider documenting this and changing the field names in the UI. This concept of "Service Name" is really confusing, especially when it's on the local machine, you are supposed to use the computer name instead. Is this documented anywhere? I hope this post gets indexed by google, because it really shouldn't take a week to figure this out.
Also, the concept of a Data Store vs. a database? What is the difference? I have databases with spatial data in them, and Map 3d can't see them, it will only allow me to create new "data stores".
Make sure your using the right dll's the SqlServerProvider.dll
will not work on spatial data types. You need the SqlServerSpatialProvider.dll. Copy the dll's into the bin folder and the rename the SqlServerProvider.dll in the providers.xml file to SqlServerSpatialProvider.dll. It will use the same UI in the data connect but use the new spatial provider.