Sharepoint instance broken in SQL 2005

This is  a SBS 2003 R2 premium server. I had a non-working Companyweb in Sharepoint so went for the KB829114 solution and reinstalled it.  Seemed to work and access to Companyweb now works. But, when I try to connect to the Sharepoint instance using SQL Management Studio I get an error :-

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: TCP

Provider, error: 0 - No connection could be made because the target machine actively refused it.) (.Net SqlClient Data

Provider)

------------------------------
For help, click:

http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10061&LinkId=20476

------------------------------
Error Number: 10061
Severity: 20
State: 0


------------------------------
Program Location:

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean

ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity,

SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean

ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean

redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString

connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString

connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object

poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection,

DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory

connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo

ci, IServerType server)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ConnectToServer(Object

connectionInfo, IDbConnection liveConnection, Boolean validateConnection)

Any ideas on what I have missed?
PHWigginsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

scuthberCommented:
The sharepoint/companyweb on sbs uses an MSDE (2000) not Express Edition. It cannot accept connections from a remote server or workstation, so if you are trying to connect from anything other than the SBS box, you won't be able to.
If you have moved to an SQL 2005 or Express Edition instance of SQL, you should be able to connect using SSMS and Windows Authentication. You need to specify the instance name correctly, which by default is computername\sharepoint.
0
scuthberCommented:
Oh, and you can do an OSQL -S hostname\sharepoint -E to connect using a trusted connection from a command prompt. What happens then?
0
PHWigginsAuthor Commented:
Hi, Thanks for your response.  
"The sharepoint/companyweb on sbs uses an MSDE (2000) not Express Edition. It cannot accept connections from a remote server or workstation, so if you are trying to connect from anything other than the SBS box, you won't be able to." - I had been able to see the databases using SSMS, does that mean I need to run the procedure to upgrade from MSDE to SQL 2005?

"If you have moved to an SQL 2005 or Express Edition instance of SQL, you should be able to connect using SSMS and Windows Authentication. You need to specify the instance name correctly, which by default is computername\sharepoint" - see above.

"Oh, and you can do an OSQL -S hostname\sharepoint -E to connect using a trusted connection from a command prompt. What happens then?" - That works OK.

Is my problem now that I need to move up to SQL2005?  I had another database stored in that instance ( I know, it was a newbie error, but it worked), and I need to recover it urgently.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

scuthberCommented:
1) You should be able to see the databases from SSMS running on the SBS server, regardless of which version they are.
2) If OSQL can connect to the instance using -E (trusted connection), there's no reason why SSMS shouldn't connect.

If you can see the database you need to recover, you need to see if it is compatibility 80 (2000/MSDE) or 90 (2005). You should be able to see this from the database properties. Is this database on the default SQL 2005 instance, or is it not attached to an instance?
0
PHWigginsAuthor Commented:
"1) You should be able to see the databases from SSMS running on the SBS server, regardless of which version they are." - Something is obviously wrong as I cannot.
"2) If OSQL can connect to the instance using -E (trusted connection), there's no reason why SSMS shouldn't connect." - But it doesn't, this is the problem we are trying to solve.

"f you can see the database you need to recover, you need to see if it is compatibility 80 (2000/MSDE) or 90 (2005). You should be able to see this from the database properties. Is this database on the default SQL 2005 instance, or is it not attached to an instance?" - Database only seems to be on a backup tape that I can see.  I can't use SSMS as it won't connect to the sharepoint instance.  

Can I somehow recreate\repair\rebulid the sharepoint instance?
0
scuthberCommented:
Yes, you can. First, you need to know that your SQL instance is OK, which it sounds if it is, as you can OSQL to it. You need to attach the database files to the instance, so get the companyweb mdf and ldf files from wherever (they'll probably be called something like STS_12345_1.mdf & ldf) and see if you can attach them using sp_attach_db, or use SMSS.
If you can at least do this, you at least know the content it OK. Likewise, you should be able to mount your original sts_config databases?
0
scuthberCommented:
Once you've established that you can mount your databases on your sql 2005 instance, I'd recommend uninstalling sharepoint, then extract the stsv2.exe installer to a folder, then run stssetup.exe remotesql=yes from a command line. This will allow you to connect to your original configuration and content databases afterwards. The sharepoint installer is buried away on one of the SBS CD's (number 3 I think), or just download it.
0
PHWigginsAuthor Commented:
Hi scuthber,

I think you have the problem the wrong way round. I have reinsatlled sharepoint and now have a sytem running on MSDE, hence the test with OSLQ.EXE works!  When I try to connect using SQLCMD.EXE ( the SQL 2005 command line tool) I can connect to the default instance, BUT NOT TO server/sharepoint instance.  What I need to do is "upgrade" the MSDE sharepoint to SQL 2005 somehow, and still be able to restore the other database into the SQL 2005 sharepoint instance.  I was obviously not clear in my previous questions, sorry about that.
0
scuthberCommented:
Sorry, my mistake. It sound's as if you already have an SQL 2005 instance on the server though? Or how are you trying to connect using SMSS?
I think (going right back to the beginning) you need to uninstall sharepoint again. Install SQL 2005 (if it's not installed already).
Attach the old database files to your SQL 2005 instance. Usually STS_Config.mdf/ldf and STS_SERVER_1.mdf/ldf (SERVER will be your server name).
Reinstall sharepoint, using the remotesql=yes option.
You need to run the application pool as a domain user (so you can grant it access to SQL & your databases). After sharepoint is installed, you will first get the configureadminvs.aspx page, then after that you get a page asking you which farm you want to connect to. You can then connect to your config databse, etc
0
PHWigginsAuthor Commented:
Hi scuthber,

I can see that my description of the problem was incomplete.  I have SBS 2003 R2 Premium with SQL 2005 installed.  This is working as I have severa database using it, notably CRM 4.0. For some reason the companyweb was not working so I reinstalled WSS. This is now working, but as you have shown above, its on the MSDE.  This reinstall has somehow wrecked the SQL SHAREPOINT instance and i can no longer connect to it.  This was quite a problem as I had stupidly installed another database in that instance.  I have now recovered that databse from backup tape and installed it in a different instance, so that particular problem is now resolved.  I would like to get sharepoint in SQL server, so what is my best chance now.  Do I remove the sharepoint instance from SQL server, then upgrade the MSDE version? I don't think reinstalling SQL Server is an option, but some sort of repair that leaves all DBs intact would be possible if that is needed to get the Sharepoint instance back.  Any thoughts on this would be much appreciated.
0
scuthberCommented:
I kind of worked that out. I think that what I said previously still is valid. See if you can mount your old database files on your default instance of sql 2005 first. Both the config database and the content db.
Uninstall your sharepoint and msde. Find the sharepoint installer on the SBS disks, or download it. The file is stsv2.exe. Extract it to a temporary folder. http://support.microsoft.com/kb/829782 shows how to do this.
Run the installer with the remotesql=yes option, this will not reinstall msde, but it dows install the web front end. When this is finished, a browser window will open and allow you to configure the sharepoint administration site. After this, you can connect to an existing config and content database. I can't be any more accurate than that without doing it again, I've slept since the last time I did it!.
This article might help you http://technet2.microsoft.com/windowsserver/WSS/en/library/c11ec25e-e87f-4898-ab01-7e5017366f601033.mspx?mfr=true but you've already done bits of it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SBS

From novice to tech pro — start learning today.