Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Server 2005 Express configuration trouble

Posted on 2011-03-14
13
553 Views
Last Modified: 2012-05-11
I have installed SQL Server 2005 Express on a Windows Server 2003 DC R2 SP2.
I have migrated an Access 2003 db to this Express Edition of SQL using ODBC and things work great when I am local to server and using SSMSE.  When my remote users load Access DB and run queries that require ODBC connection then it fails.  I have looked at most things from client side...to no avail.
Googling my way through this issue, I have found some helpful tips but no solution.
I am unable to open SS Surface Area Configuration and Configuration Manager.  I get some WMI errors and/or access issues.
I have tried upgrading to full version Standard SQL and it failed during install.  
I am frustrated and would like to blow it away and re-install everything, but I need to retain my 3 different dbs.  Please either help with a fix or tell me the safest way to back up db and reinstall Server and bring my db's back to life.

Thanks in advance.

Army
0
Comment
Question by:armynt4
  • 8
  • 3
  • 2
13 Comments
 
LVL 3

Expert Comment

by:Chadinzski
ID: 35132115
Since the Access queries work when you're local, the first thing I'd do is a TELNET test on the remote workstation.  Typically the port is 1433, but I'd probably check the SQL server to verify.  I can help you verify the port even if the SQL GUI is not loading.  I'd run the telnet test on the server and the remote machine.

Once telnet works, next up I'd load the SQL management studio on both the server and a remote machine.
http://www.microsoft.com/downloads/en/details.aspx?familyid=56AD557C-03E6-4369-9C1D-E81B33D8026B&displaylang=en

Then you can use the management studio to login as the same user as Access to verify it's not a security issue.
0
 
LVL 2

Author Comment

by:armynt4
ID: 35132306
Telnet does not from workstation to Server and vice versa.  I am unable to get to where I can enable the TCP/IP or named pipes on the server.

SQL Management studio is loaded on the server and works fine.

Is that link going to work with the SQL Server 2005 Express?  It is a 2008 link?
0
 
LVL 3

Expert Comment

by:Chadinzski
ID: 35132622
Telnet test will and should only work from workstation to server, or server to server and not server to workstation.

Yes- the newer versions of management studio work on older versions.  I use, for example the 2008 edition even with SQL 2000.

Have you tried all of your Windows Updates first to see if that fixes the problem with getting the surface area tool to work?  By default SQL disables connections from machines other than server, so we're really going to want those tools to work.  Use Event Viewer to get more details when launching the tool and when it crashes.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Author Comment

by:armynt4
ID: 35132674
Good that's what I thought.  I will load tool on workstation and take a look at event logs and updates.  I will post some event log errors if they apply.  Thanks.
0
 
LVL 2

Author Comment

by:armynt4
ID: 35133133
When attempting to connect from WS to SRV using ssms I get this error.

TITLE: Connect to Server
------------------------------

Cannot connect to SERVER\SQLEXPRESS.

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1265)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=1265&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
0
 
LVL 2

Author Comment

by:armynt4
ID: 35133137
Didn't see anything in event logs when attempting to access the Surface Config App.
0
 
LVL 2

Author Comment

by:armynt4
ID: 35134313
Definitely believe if I can get the configuration manager or surface config manager working I can solve the remote access issue.  Not sure if there is anything else I can do to provide more evidence.

Here is what happens when I try to go into SQLSAC.....

TITLE: Surface Area Configuration
------------------------------

Computer localhost does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required Windows Management Instrumentation components and then try again. (SQLSAC)

------------------------------
ADDITIONAL INFORMATION:

An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.InnerWmiException&LinkId=20476

------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------

The operation could not be completed. (WinMgmt)

------------------------------
BUTTONS:

OK
------------------------------
0
 
LVL 2

Expert Comment

by:Thamjin
ID: 35136332
WMI error, try this.
http://support.microsoft.com/default.aspx?scid=kb%3bEN-US%3b956013

Crude idea to copy database.

1 stop the current instance of the database engine instance via CMD->RUN->services.msc
2 Install another instance of the database engine, on this machine or on another machine
3 Copy the MDF/NDF/LDF Files from the 3 database to another folder on the new database engine
4 Attach the databases to it.
5 Use the sql managment studio to run the store procedure to link the sql admin account to the server admin account for permission and security.

Look for orphan users account
http://msdn.microsoft.com/en-us/library/ms174378.aspx
http://support.microsoft.com/kb/314546 
0
 
LVL 2

Author Comment

by:armynt4
ID: 35149369
I tried the KB fix for the WMI error and it changed nothing.

But that is close to the reason of why this is happening...I had loaded SQL Server versions for testing and now it looks like there is some confusion with the server and the multiple attempts of loading and removing SQL versions.
I have always installed 32bit versions of SQL but 2000, 2008 Express, 2005 Express.
I would like to move the databases off of this server and then clean the server in order to get it working back on the server again.
I believe I could get them moved...and that may be where I start.

0
 
LVL 2

Author Comment

by:armynt4
ID: 35151493
Ok so I have pulled the tables I needed moved to SQL back into MS Access.  This will keep the data tables safe until I get SQL 2005 installed and running properly on my server.
I would like to install SQL Server 2005 Standard on my server.  I have installed and uninstalled numerous flavors of MS SQL on this server and I believe that is what caused my initial problem.  

I would love some guidance on how to completely remove MS SQL and any remnants of previous installs so this server won't run into obvious conflicts.  

I appreciate the help.
0
 
LVL 2

Accepted Solution

by:
Thamjin earned 250 total points
ID: 35213455
0
 
LVL 3

Assisted Solution

by:Chadinzski
Chadinzski earned 250 total points
ID: 35218693
Side note:  Moving the tables between Access and SQL can be sometimes a little tedious.  If Access is working for you, then you might not need Microsoft SQL 2005 Standard and you might get away with using Microsoft SQL 2005 Express in the meantime.  You can install the Express edition on any machine and make it the temporary server for the site.  Then once you get this machine installation issue resolved, all you'd have to do is change the SQL server name and you're done.  It'd let you port Access over to SQL and stay on SQL while you're fighting the issue out.  I'm running a number of my Access databases in both the Express and Standard worlds.
0
 
LVL 2

Author Comment

by:armynt4
ID: 35234940
I did reload SQL standard.  Everything is back to normal. Thanks for all the help.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question