PHWiggins
asked on
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.SqlI
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity,
SqlConnection owningObject)
at System.Data.SqlClient.SqlI
ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlI
redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlI
connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlI
connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlC
poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.D
DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
connectionFactory)
at System.Data.SqlClient.SqlC
at Microsoft.SqlServer.Manage
ci, IServerType server)
at Microsoft.SqlServer.Manage
connectionInfo, IDbConnection liveConnection, Boolean validateConnection)
Any ideas on what I have missed?
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.SqlI
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity,
SqlConnection owningObject)
at System.Data.SqlClient.SqlI
ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlI
redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlI
connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlI
connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlC
poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.D
DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
connectionFactory)
at System.Data.SqlClient.SqlC
at Microsoft.SqlServer.Manage
ci, IServerType server)
at Microsoft.SqlServer.Manage
connectionInfo, IDbConnection liveConnection, Boolean validateConnection)
Any ideas on what I have missed?
Oh, and you can do an OSQL -S hostname\sharepoint -E to connect using a trusted connection from a command prompt. What happens then?
ASKER
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.
"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.
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?
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?
ASKER
"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?
"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?
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?
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?
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.
ASKER
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.
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.
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.