This is an entirely new world that has been completely thrust upon me (although I'm enjoying the challenge) so if say something completely stupid, please forgive me.
I am trying to create a new enterprise geodatabase on our sql server. This was set up 4+ years ago, and has not been modified since. The guy who previously handled this side of the GIS work left, and it is now up to me to figure it all out. First off, a little bit about how we are setup. We have two machines, SQLSERVER and MAPSERVER. SQLSERVER is a virtual machine that houses our sql databases. MAPSERVER is a virtual machine that houses our installation of server for arcgis.
Now I will attempt to take you through the my workflow in creating the database and along the way, introduce the problem we are having. First thing I did on my local PC (MYPC for the sake of this post) is to open catalog and run the *Create Enterprise Geodatabase* tool. For the database platform, i have SQL_Server, obviously. Instance is SQLSERVER. I name the database MYDB. Operating System authentication is **checked** (That is how all of our existing and functional databases are setup). Sde Owned Schema is **unchecked**. For the authorization file, I found the keycodes file located on MAPSERVER (where server for arcgis was installed). I copied that to the dekstop of MYPC for ease of access in running this tool and the ESRI workflow indicated that you could. All other options in the tool are blank because they became grayed out once the Operating System Authentication was checked and the Sde owned schema was unchecked. At this point I ran the tool and the database was created successfully.
I logged into SQLSERVER at this point and opened up SQL Server Management Studio (2012) went to Security>Logins>SQLSERVER\arcgis user>right click properties>User Mapping. Once there, I mapped the newly created database, and added the role of db_datareader and db_datawriter.
Finally I went to register the data store with our server. I did so using catalog for arcgis as I never could get it to work with server manager. The data store registered successfully and a green check mark appeared beside it.
I thought that was mission success, however it was not. Once I loaded a new feature dataset onto the new enterprise geodatabase, applied privileges to SQLSERVER using Catalog>Manage>Privileges>SQLSERVER/arcgis. I was ready to publish it. I used the arcmap>file>share as...>service>and went about configuring the service in the desired way. I then attempted to publish it but got the following error message.
As instructed, I went and scoped out the server log. It had the following errors in addition to the one from the picture above:
The server log makes it sound like the data does not exist. I have verified that it does however, through seeing it in both Catalog and SQL Server Management studio. The data is 100% there. It just isn't seeing it for some reason. I have tried redoing the process numerous times and have met gotten the same result every time. It has to be something I did wrong in creating this new database because all of our existing ones still publish services just fine.
So that is where I am currently at. I have spent the last two days trying to figure this out. I figured I would check with you guys rather than continuing to beat my head against the wall. For those of you that stuck around and read all of this, I really appreciate it!
Try manually adding privileges to the feature class for the arcgis service account using the Catalog window in ArcMap by right-clicking on the feature class > Manage > Privileges. Afterwards, see if you are able to publish the service.
Also, is the arcgis service account a local or domain account?
I forgot to mention, but I performed this step as well. I have edited the post to reflect that. And forgive me as I'm still learning this side of arcgis, but when you ask if the service account is a local or domain account, could you expand on that question? I'm not quite following. How do I find that information?
The arcgis service account is the user you are granting db_datareader and db_datawrite privileges to in SSMS. If this is a domain account it will be something like: domain\arcgis
If it is a local account it will be something like: localmachine\arcgis
The name of the machine in SSMS that I granted the permissions to was SQLSERVER\arcgis. I think that would make it a local account. I'm not sure if it matters or not, however it is on a virtual machine.
Looks to be a local account. This local account, arcgis, will need to exist on MAPSERVER will the same password. Since you are able to publish data from other geodatabases, I'm thinking this may not be the issue though. Can you temporarily grant the SQLSERVER\arcgis user SYSADMIN privileges and see if you are able to publish data?
I don't know if you already solved your problem. I had a similar problem and the solution in my case was to change the password. I used the special character for "pound" (£) at the end of the password. After removing that, everything was fine. Perhaps there are other special characters which may cause this problem.
Hope it helps.
Yes, it's the SQL database password, but I'm using SQL Server Authentication. I think that the cause of our problem was the registration in the data store from ArcGIS Server. I even have a Dollar-sign ($) in the middle of my password and that doen't harm. Perhaps it's a special constellation of the password that harms.
Try to change your password to a simple one and test it. Don't forget to validate the registered databases in the data store afterwards.