Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5664
  • Last Modified:

Help with connecting MS Office Accounting 2008 to SQL Server 2005

When trying to "Start a new company" (my first company after installing), after about 10 seconds of the dialog saying it is saving the new company and creating the database, I receive the message:

Microsoft Office Accounting cannot create the company because it cannot connect to the specified location.

The goal is to have the multi-user environment for our small business.  Here is what I have done to get to this point (all of this work done on the server, with a user with Administrative rights):

- Created new instance of Sql Server 2005, and named it MSSMLBIZ.  
    - Using Sql Server Configuration  Manager, set instance to use TCP Port 5356.
    - Using Sql Server Surface Area Configuration, set instance  to allow Local and remote
      connections using both TCP/IP and named pipes.
 
- Installed Microsoft Office Accounting 2008 on a Windows 2003 server running Sql Server 2005.

- Tried to start a new company, only to receive the message described above.

- Tried to open a sample company, only to receive the message a long, seemingly useless message
   saying, essentially, that it failed to connect to the server and that SQL Server may not be set to
   allow remote connections.  First, I'm on the server.  Second, I thought I had taken care of allowing
   remote connections in the Surface Area Configuration tool.

I'm looking for help to get this multi-user, SQL Server installation MS Office Accounting 2008 off the ground, and I really need some help.

Thanks,

Sean
0
SeanInIowa
Asked:
SeanInIowa
  • 18
  • 16
  • 9
  • +3
2 Solutions
 
Joanne M. OrzechManager, Document Services CenterCommented:
0
 
Jim P.Commented:
Never having heard of Accounting 2008 -- shooting from the hip:

>> - Using Sql Server Configuration  Manager, set instance to use TCP Port 5356.

With SQL 2005 they made a change that multiple instances can run on the default ports of 1433/1434. You do need to have the SQL Server Browser service running for it to work properly -- Computer Management --> Services.

>> connections using both TCP/IP and named pipes.

Disable Named Pipes. They will bring you nothing but grief. The protocol was designed for very small, very fast, very stable networks. I've seen it work for about 2 months before encountering problems. Royal PITA.

I'm not sure what authentication Acctg uses. But change the SQL Authentication to Mixed mode for now.  You can always switch it later.
0
 
SeanInIowaAuthor Commented:
JOrzech...Thanks for the reminder.  I'm actually running 2008, so I applied the patch that is available for 2008.  No apparent difference.

jimpen...The port 5356 comes from the installation guide for the software.  I haven't seen any way to change the configuration in the software, so I set up the new instance to use 5356 per the installation guide.  I disabled named pipes per your message.  The software uses Windows Authentication, but I checked to make sure I was in mixed mode, just to be sure.  I created a database and a table in the database using SSMS under the login I am currently logged in as (with Admin rights).  It worked fine, so it shouldn't be that the user doesn't have the necessary rights in Sql Server

All that said, I'm still receiving the same message when trying to start a new company...

Microsoft Office Accounting cannot create the company because it cannot connect to the specified location.

Thanks,

Sean
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jim P.Commented:
Does it ask for the server and instance?

Try doing it as IP instead of server name (10.xx.x.x\MSSMLBIZ) instead and see if it works. It might be a DNS issue if that works.

0
 
SeanInIowaAuthor Commented:
The dialog itself asks for "Instance name:".

I have tried the server name\MSSMLBIZ and ip\MSSMLBIZ methods.  Since I'm actually on the server, I've even tried localhost\MSSMLBIZ.  Still no success...same message., and I am still not having success.

Thanks,

Sean
0
 
Jim P.Commented:
Dumb thought: What account are SQL Service accounts running under -- Local System or a Domain User account?
0
 
SeanInIowaAuthor Commented:
I appreciate you sticking with me...

The SQL Server services are all running under Local System

BTW...I'm leaving the office for the day.

Thanks,

Sean
0
 
Jim P.Commented:
>> The SQL Server services are all running under Local System

I think that may be your problem. For SQL to use Windows Authentication it needs to have a just plain domain user account to do authentication. The way Windows authentication works in an AD environment is that the app/ws/user logging on says here's my SAM and encrypted key to the SQL Server. The SQL server then goes to the AD and says "Here I am; do you recognize me?" The AD controller says "Yes, I recognize you are an account in the AD." Then the SQL says "This userid wants to talk to me, is he valid?" The AD takes the two items and then says "Yes, he is valid. (or not)".

When you are running SQL as Local System the AD says "You aren't a valid account in the domain. Go away." and the authentication process ends.
0
 
SeanInIowaAuthor Commented:
Changed the SQL Server service to run with a AD user, and still no luck.  I do not see anything in the SQL Server log that indicates that the app is even trying to connect.  Is there some type of tracing that might be helpful here?  

Thanks,

Sean
0
 
Jim P.Commented:
>> Changed the SQL Server service

Also the agent and the rest? And dumb question, did you restart the services, including the SQL Browser?

Check out Sysinternals (http://technet.microsoft.com/en-us/sysinternals/default.aspx) Do filemon, regmon and the TCPView. I would start with the TCPView.



0
 
SeanInIowaAuthor Commented:
I had changed the SQL Server service and the Agent service, and restarted them.  I've also now changed the Browser service and restarted it, with seemingly no effect.

I see in TCPView a TCP Process for sqlservr.exe listening on Local Address listing the port 5356.  Other than that I'm not sure what I am looking for with the sysinternal tools.

Thanks,

Sean


0
 
Jim P.Commented:
You're looking for the Accounting software trying to connect to the port.

I know the manual says port 5356 -- but at this pint, try changing it back to 1433/1434.
0
 
SeanInIowaAuthor Commented:
Okay.  I'm really getting frustrated because I've done about everything I can think of.  I have tried nearly every combination of Instance Name/Port that I can come up with, all to no avail.  I have been able to connect to the instance SSMS using Windows authentication any of the last 10 ways I've had SQL Server configured.  However, I've never had the application successfully connect, nor have I seen anything in the logs to indicate that it has even tried, much less been successful.

Thanks,

Sean
0
 
Jim P.Commented:
If you aren't seeing anything in TCPView, fire up FileMon and RegMon and see what is happening there.
0
 
SeanInIowaAuthor Commented:
Sorry I've been away.

The only thing I noticed of interest was a log file being generated.  In it I find the message:

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I have Googled this message, and tried everything I found.  Still no luck.

Sean
0
 
Jim P.Commented:
And the browser service is up?

Can you confirm the port change was done something like in the article below?

Using Non-Standard Port for SQL Server
http://www.databasejournal.com/features/mssql/article.php/3689846/Using-Non-Standard-Port-for-SQL-Server.htm

Maybe pull down this white paper and see if it covers something.
http://whitepapers.techrepublic.com.com/abstract.aspx?docid=350238
0
 
SeanInIowaAuthor Commented:
Thanks for the reference material.  I hadn't seen the first article, but I've been working from the white paper.  And yes, the browser service is up.

This might be helpful information.  I used osql and was able to connect to the instance just fine.  I did this both on the server and from another computer.  I'm also able to see with netstat that there is a connection on port 5356.  

I'm deducing, then, that remote connecting is enabled correctly and that I am listening on port 5356.  So, I think the problem is more related to the Microsoft Office Accounting application itself, and how it is attempting to connect to the database.

Thanks,

Sean


0
 
Jim P.Commented:
Since you're using SQL Express -- do you have the Microsoft SQL Server Management Studio Express -- link below. That gives you a GUI into the SQL Server instance and if that can connect it give you tools to see if anything is hitting -- fire the Activity Monitor under Management. Set it to auto-refresh

http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en
0
 
SeanInIowaAuthor Commented:
I'm actually not using SQL Express.  That is really the point of this whole exercise.  The app, on its own, would create a SQL Express instance for a single user.  I'm trying to follow the instructions for creating a multi-user environment on SQL Server 2005.

I already have SSMS.  I did not see anything hit the instance in the Activity Monitor.  I hadn't used this part of the tool before, but it does seem to confirm what I said about the application not getting to the database.  Unfortunately, the configuration for this is really very simple...server\instance.  Seems hard to mess that up.

Thanks,

Sean


0
 
Jim P.Commented:
Whats the security setup for the user that is logging in?

Just for grins try giving the userid sysadmin on the Server Roles.

I agree this should be easy.

Can I get a screen shot(s) of what you are looking at when trying to login?
0
 
SeanInIowaAuthor Commented:
The instance is set with mixed mode.  The application itself using Windows Authentication.  As such, the app doesn't really ask me to login.  Just to eliminate any issues in testing, the user has wide open authority in the instance.  I was able to use this user with osql successfully.

Thanks,

Sean

OA2008-Error.JPG
0
 
Jim P.Commented:
Have you tried changing the specified location to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data (or wherever your  Master DB for the instance) is located?

And do you have a screen shot of the login page?

Another thing to do is try doing a Run As (right click on the icon -- should be about the third one down) and using the same userid as the SQL Services.
0
 
SeanInIowaAuthor Commented:
The directory you are seeing is not actually where the database itself is stored.  It is a file used by the application.

As I mentioned, there is no login page for the application.  It is using Windows authentication, so it is using the user I am logged in as.  I am logged in as the same user that is running the db services, so there shouldn't be any need to Run As.

The whole setup right now is terribly unsecure, and I would never run it this way for real.  I've opened things wide in order to get it to work at all.

Thanks,

Sean


0
 
Jim P.Commented:
Where do you select the SQL Server?
0
 
SeanInIowaAuthor Commented:
Attached is the screenshot of the dialog where I specify the instance.  The following is the text from the help on this dialog:

Displays your local or remote connection to Microsoft SQL Server, which is identified by an instance name. Enter an instance name when connecting to local or remote computers.Displays your local or remote connection to Microsoft SQL Server, which is  identified by an instance name. Enter an instance name when connecting to local  or remote computers.
I've tried using the IP address instead of the server name.  I've tried the service name.  I've tried putting the server name in the hosts file.  I've tried forward-slashes, back-slashes, double back-slashes.  The only thing that seems to produce a different result (an error of a different type) is when I just put MSSMLBIZ with no server (I think this is how it would work with the local, SSE implementation).

Since I didn't see anything in the Activity Monitor, it appears the app isn't even getting to SQL Server.  That seems to jibe with the error message I included earlier from the application log.

Thanks,

Sean
0
 
SeanInIowaAuthor Commented:
Here's the dialog

OA2008-Data.JPG
0
 
Jim P.Commented:
This is getting into a total guess -- In this Q (http:/Q_23915227.html) they were asking about how to "deploy" an app. The first response was to hold the instance info as [sServerName = ".\SQLEXPRESS"]. The MOA may be doing that.

Run throught he registry and see if you can find the "MSSMLBIZ" string anywhere (hopefully under HKLM) and see what happens if you modify it there. This may be a case of having to force the change with a REGEDIT instead of using the front end.


http://www.experts-exchange.com/Q_23915227.html
0
 
asljamaicaCommented:
Let me answer the question that have puzzled soo many of you. Very Simple. Change you instance back to MSSQLSERVER. That will install the database under that instance. There was never a need to create a new instance. Try that and then tell me how brilliant i am.

Take care.
0
 
asljamaicaCommented:
Listen carefully. I will only say this once. Problem to your solution:

1. Install sql server express even though you have sql server 2005 installed. It will not work otherwise.
2. Using the configuration manager to enable remote connections (tcp/ip and named pipes) on sql express
3. remove port 5356 from sql server 2005 and put on sql server express (using configuration manager)

things to remember:
1. You can configure and manipulate sql express using sql 2005 server management studio
2. sql server 2005 and sql express will not start if they are both using the same port
3. sql express must only using port 5356 NOT 1433. sql server 2005 can continue using port 1433

This resolved my issue after weeks of frustration. If you have and other questions, please email me directly (asljamaica@yahoo.com).

Bye.

0
 
asljamaicaCommented:
sorry, i meant solution to your problem.
0
 
SeanInIowaAuthor Commented:
SImply using SQL Server Express was the starting point...it still doesn't answer how to use SQL Server.

That is okay, though.  Frankly, I give up.  Late in the afternoon on Friday I uninstalled the new SQL Server Instance (that MS said to install!) and did the Office Accounting installation again using SQL Server Express.  I will just have to figure out how to make that work for me.

Thank you to jimpen for your patience in working through this with me!  I will figure out how to award you points for all your effort.

And as for asljamaica...I appreciate the thought in your registering for EE.  However, you're going to have to work on your presentation.  Saying things like  "tell me how brilliant I am" and  "Listen carefully. I will only say this once" are not going to win you too many followers.

Sean
0
 
SeanInIowaAuthor Commented:
Although we didn't really end up with a solution, jimpen took a very logical approach to looking at the problem. In the end, I simply ran out of time to mess with it.  We needed to move forward here in the office.
0
 
Jim P.Commented:
>> SQL Server Express was the starting point...it still doesn't
>> answer how to use SQL Server.

I'm guessing this is an M$ thing to force you onto the next step up or something similar. We have a few apps that can't  work with full blown SQL, but only the MSDE/SQL Express versions.

We have had some success with getting some of them to cave and allow any version of SQL. I don't think you'll get anywhere with M$ though.

I'm sorry I couldn't give you a better answer. May all your days get brighter and brighter.
0
 
asljamaicaCommented:
i have done it and this is how it works:

You would install SQL Server 2005 using the default instance (like you did). You then install the second instance (MSSMLBIZ). You then remove the port 5356 from the default instance and place it on the MSSMLBIZ instance ONLY. (that was your problem). You then ensure that only port 5356 is used on the second instance (MSSMLBIZ). You then start both instance and start the installation. When the installation starts, simply choose CUSTOMISE. You then ensure you select the MSSMLBIZ instance and carry out your installation.

When installing the client, you MUST use the instance (SERVERNAME\INSTANCENAME).,
eg: AS01\MSSMLBIZ for the instance when you select customise during the client installation.

Let me know how the installation goes. I did it this morning because i was having the same problem and resorted to using SQL Express lastnight. But this morning i decided i wasn't going to give up and the above resolved the issue. REMEMBER, only the MSSMLBIZ instance must use PORT 5356 ONLY. Also, ensure that remote connections is enabled using TCP/IP and NamedPipes.

0
 
asljamaicaCommented:
Please note, when installing MS ACCOUNTING on the server, you cannot use SERVERNAME\INSTANCE as the instance name. YOU MUST USE ONLY INSTANCE NAME. On the workstations, you use SERVERNAME\INSTANCENAME.

Good Luck.
0
 
asljamaicaCommented:
I am also using MS ACCounting, but over the internet, and it is SSSLLLOOOWWW. I thought it would be fast due to the fact that it has a database backend, but i am very DISAPPOINTED. My whole reason for choosing it was due to its database backend. I have remote sales reps who will using it over the internet.

Let me know what you think about the application.

Take Care.
0
 
TNGITCommented:
SeanInIowa I have almost exactly the same problem as you.  I really don't want to install SQL Express though - this is my home workstation and one installation of SQL Server is more than enough already.  

The only difference between mine an your problems is I am attempting installation with SQL Server 2008. I avoided installing SQL Express at installation by choosing advanced and installing just the client.  Then, after going to the Data Utilities dialog, I select the 'Settings' tab and type my instance name.  

When I go to create my company it instantly gives the error 'the database that you requested could not be opened, or access is denied', followed by a polite hyperlink to 'click here if this error message was not useful' which merely sends an error report to get lost at MS.

I'm 90% sure the instance name is correct as if I choose an incorrect instance name after trying to create my company it hangs for about 10secs and then gives a difference error message.  

I'm out of ideas.  And I really, really don't want to put on SQL Express.
0
 
asljamaicaCommented:
Did you read my comment. You must install an instance named MSSMLBIZ. Even though MS says you can install it under the default instance, i never got that to work. After doing that, you must install the full program on the server. This installation creates the database in the new instance. Installing the client DOES NOT CREATE THE DATABASE. Once that is completed, you can then install the client on the workstation with the instance name MSSMLBIZ. Also, please remenber to enable remote connections (TCP/IP and NamedPipes) and enable port 5356 in Dynamic Ports. Once this is done, (first enable port 5356 after installing the instance and before installing MS Accounting) you will be okay. Try that and let me know the result.

Also, ensure that during the installation on the server, you choose customize and select the instance (MSSMLBIZ ) that you are going to install the database under.

Good Luck
0
 
Jim P.Commented:
>> I'm out of ideas.  And I really, really don't want to put on SQL Express.

TNGIT,

I think M$ has locked the Office Accounting into SQL Express only. All the work I did with Sean pretty much points that way.

I think it is because they are trying to force you to upscale to Great Plains or something similar.

I'm betting if you had the profiler up when you were trying to connect the MOA, you would see a query go by for "select @@version" and the reply coming back being something like below stops it. In SQL 2000 you could actually manipulate the DB tables to change that, but this new version locks it out.



Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) 
	Feb  9 2007 22:47:07 
	Copyright (c) 1988-2005 Microsoft Corporation
	Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Open in new window

0
 
asljamaicaCommented:
Did anyone try and follow the instructions i posted. Microsoft is not trying to lockout out anyone. It works with SQL 2005/2008 because my installation works. Anyway, wish you ALL the luck.

Regards,
0
 
TNGITCommented:
I've just tried your solution asljamaica with no luck - it does exactly the same thing as what happened when I tried to connect it to the default instance.  

jimpen I think I'm starting to agree with you - the last query in the profiler, after Office Accounting rejected the database, was:

SELECT CASE WHEN patindex('%.%.%.%',@@version) > 0 THEN cast(substring(@@version, patindex('%.%.%.%',@@version)-2,charindex('.',ltrim(substring(@@version, patindex('%.%.%.%',@@version)-2,8)))) as int) ELSE 0 END

It looks like its checking the version then rejecting it, returning an incredibly unuseful error message of 'the database that you requested could not be opened, or access is denied'.

If this is the case, asljamaica I don't understand how you got it working - are you using Microsoft Office Accounting 2008 Professional?
0
 
Jim P.Commented:
http:/Q_23895091.html#23025128

>> 1. Install sql server express even though you have
>> sql server 2005 installed. It will not work otherwise.

He's using SQL Express. I think you are stuck with it.

The only way around it that I can think of is to have it install into SQL Express and then move the DB to a full version and create a linked/front-end in the SQL Express.
0
 
TNGITCommented:
Ah I see, I missed that first time around.

I will continuing monitoring this thread to see if anyone does come up with a solution.  
0
 
asljamaicaCommented:
I am NOT using SQL Express. I am using SQL Server 2005. It is very easy to setup. I will go through it one more time.

1. Install SQL Server 2005 using its default instance (MSSQLSERVER)
2. Install SQL Server 2005 to create a new instance (I suspect you know how to create a new instance, simply install just the database services, then install service pack 2)
3. Name this new instance MSSMLBIZ
4. Ensure that all the TCP Port for MSSMLBIZ (using SQL Configuration Manager) are set to 5356. Including the IPAll ports.
5. Ensure you give your clients access to the new instance (using SQL Surface Area Configuration. Select the instance (MSSMLBIZ), select Remote Connections, then select Local and Remote Connections (using both TCP/IP and NamedPipes).
6. Click Apply, OK
5. You then proceed to restart the services, or restart the server. Ensure all SQL services are started (or the important ones)
6. You then install MS Accouting 2008/2009 and select Customize. You then select the SQL Instance Name Office Accounting Should Use from the Instance List (MSSMLBIZ)
7. You then complete the installation. You must install the full version on the server as that is the how you create the database for the application.

If you have any questions, give me your number and i will call you. I spent a lot of time figuring this one out and i know the frustration you must be going through. It is not difficult. You just need to ensure you install an instance named MSSMLBIZ and MS Accounting will not work under any other instance even though their documentation say so.

Again, i am not using SQL Express. I was but i got rid of that a long time ago and even did a re-installation last night because i uninstalled 2008 to install 2009 and uninstalled my SQL Server Instance (MSSMLBIZ) so i had to do the installation all over again.

Good Luck
0
 
Jim P.Commented:
I'm betting that they deliberately hobbled MOA Express to using SQL Express so that they can force you to go to the paid version. Before I would pay though, I would make sure that you can use the full SQL with it.

--------------------------------------------------------------------
>> Pricing and licensing
>> How much does Accounting Express 2009 cost?
>> Accounting Express 20098 is available for free as a download.

http://office.microsoft.com/en-us/accountingexpress/HA103347291033.aspx
--------------------------------------------------------------------

The other thought, depending on what you are doing, is if you need the SQL agent is to look at SQLScheduler  (http://www.lazycoding.com/home.aspx). Its a free extension from a 3rd party that can handle your backups and maint tasks for SQL Express.
0
 
TNGITCommented:
asljamaica: I did exactly what you said, to the word, using SQL 2008, and it didn't work.  It gave the same error message.  I am convinced that it queries the database for its version and on seeing it is SQL2008 promptly disallows it.

I'm not using Account Express either, I've got the professional version.  I don't think MSOffice Accounting 2008 supports SQL Server 2008.
0
 
Jim P.Commented:
I'd call MS and complain.

http://office.microsoft.com/en-us/accounting/HA102462291033.aspx

Hide Will I outgrow Office Accounting Professional 2008?

Office Accounting Professional 2008 is built to support small businesses that have 25 or fewer PCs and less than 50 employees. It can be configured as a single user desktop installation by using Microsoft SQL Server Express 2005. It can also be configured for multiple users in a peer-to-peer environment or deployed on a Windows Small Business Server computer with SQL Server. Businesses that outgrow Office Accounting Professional 2008 can migrate to Microsoft Dynamics.


http://www.techsupportforum.com/microsoft-support/windows-vista-support/215014-solved-microsoft-office-accounting-2008-fail-install.html

http://www.techsupportforum.com/microsoft-support/windows-vista-support/215016-solved-windows-vista-updates-fail-install.html
0
 
JCAlexandresCommented:
When I bought Office Accounting 2008 Professional, they told me it could be used with either the SQL Express bundled database server, or a full fledged SQL database server, I am finding hard to configure with a full SQL database.  I bought this package ot use in a small office the company I work for owns, with less than 10 accounitng users.
I have to add that at corporate I use MS SQL Enterprise 2000/2005, with many instances of databases, and never had a problem, working with this application is giving me green hair!
I am also looking for a solution to be able to run the said software from a full fledged SQL server rather than the express edition so our developers can connect to the database and add objects.

0
 
SeanInIowaAuthor Commented:
The only thing I can offer is that I eventually gave up.  Sorry!

Sean
0
 
JCAlexandresCommented:
I followed all the steps asljamaica posted and I am successfuly using MS Office Accounting with MS SQL 2005, so the instance name/port fix is a trick to make Office Accounting code work.

I will suggest Microsoft to provide a script to users of the paid professional version, so they can install properly, I guess for now the work-around is the best solution.

Thank you very much asljamaica for your patience and help!
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 18
  • 16
  • 9
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now