[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Sharepoint instance broken in SQL 2005

Posted on 2008-02-12
11
Medium Priority
?
1,341 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:PHWiggins
  • 7
  • 4
11 Comments
 
LVL 5

Expert Comment

by:scuthber
ID: 20874771
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
 
LVL 5

Expert Comment

by:scuthber
ID: 20874795
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
 

Author Comment

by:PHWiggins
ID: 20875150
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
LVL 5

Expert Comment

by:scuthber
ID: 20875213
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
 

Author Comment

by:PHWiggins
ID: 20875316
"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
 
LVL 5

Expert Comment

by:scuthber
ID: 20875857
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
 
LVL 5

Expert Comment

by:scuthber
ID: 20876292
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
 

Author Comment

by:PHWiggins
ID: 20876570
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
 
LVL 5

Expert Comment

by:scuthber
ID: 20883409
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
 

Author Comment

by:PHWiggins
ID: 20894937
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
 
LVL 5

Accepted Solution

by:
scuthber earned 1500 total points
ID: 20900246
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I work for a company that primarily works with small businesses as their outsourced IT vendor. As such the majority of these customers utilize some version of Small Business Server. Due to the economics of running a small business, many of these cus…
Organisation is organized in a pattern to flow the day to day business, every application and system is interdepended on each other and when very important “Exchange Server downtime” happened.
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses
Course of the Month10 days, 10 hours left to enroll

612 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