Solved

SQL Server 2005 Express configuration trouble

Posted on 2011-03-14
13
547 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now