As we start using SQL Azure more and more for storing data, we had to come up with a easy and inexpensive way to backup Azure databases. There is a number of various tools available to backup Azure databases, but they usually require a separate install and they are never free. Although, sometimes, they are fairly inexpensive. I like free ones better though.
So, after a bit of research I have discovered an easy way to backup SQL Azure databases to my on-premise (offsite) SQL Server: SQL Data Sync. D’oh! This is an existing functionality in Azure, and it can be accessed through an “old” Windows Azure portal interface (https://windows.azure.com). I am not going to write step-by-step instructions because, in this case, user interface is actually very intuitive and once you get to Data Sync part of Azure portal, you will know what to do. Good luck!
{ Ping me, if you need any help or have any questions about this article }
While installing SharePoint 2013 prerequisites on Windows Server 2012 and SQL Server 2012, I have received the error "There was an error installing the prerequisites..." After checking out the logs (under %TEMP%\prerequisiteinstaller.<date>.<time>.log), you quickly learn that prerequisite install failed because of Microsoft SQL Server 2008 R2 SP1 Native Client. To bypass this problem, you can manually download Microsoft SQL Server 2008 R2 SP1 Native Client from http://download.microsoft.com/download/9/1/3/9138773A-505D-43E2-AC08-9A77E1E0490B/1033/x64/sqlncli.msi and install it. After you manually download Microsoft SQL Server 2008 R2 SP1 Native Client, go ahead and restart SharePoint 2013 prerequisite installer. Now SharePoint 2013 prerequisites should install successfully. J
If you're getting the error "The file exists. (Exception from HRESULT: 0x80070050)", when you attempt to access your SharePoint site, or even SharePoint Central Administration site, the chances are that some (or all) SIDs on your server were changed. This has probably happened because you have removed and recreated some user accounts that you use to run SharePoint services, or you have run sysprep on your server, or you have recently turned your SharePoint server into Active Directory domain controller. It's not a good idea to do any of those things with your SharePoint server, but I'm not here to judge – I'm here to help J
Please note, that the steps described below is a "low-tech" solution to the problem, which requires you to actually understand what you're about to do. USE AT YOUR OWN RISK!!! And, of course, do not forget about backups, backups, backups. Also, I would like to point out the credit for this solution goes to the blog and his/her author that is no longer online.
Anyway, to solve "The file exists. (Exception from HRESULT: 0x80070050)…" error:
- Login to your SharePoint and open SQL Server Management Studio. Create New Query
- To get the list of Site IDs you need to use SharePoint_AdminContent_* database for Central Administration Site and run the following query:
SELECT s.Id, w.FullUrl FROM Sites s inner join Webs w on s.RootWebId = w.Id
- As a result of the query above, you will see all Site IDs for the selected database. Now, using that data, run a new query:
SELECT * FROM UserInfo WHERE tp_Login='Hostname/Username' and tp_SiteID='XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'
where tp_Login is the hostname and username using which you're trying to login to SharePoint; and tp_SiteID is the site ID from the previous query
-
As a result of the query above, you will see the SID for the selected user. Now, here comes the tricky part, you need to get SID number for the user account that you're logging in with (in my case, it was Administrator account) and convert it to hexadecimal format:
- To get SID for a user account, you can use PSTOOLS utility. Run PSGETSID \\HOSTNAME USERNAME to get SID info
- To convert the SID to hexadecimal format, use the following VB script and run it against the SID obtained in the previous step: sid2gex.vbs mySID, where my mySID is the SID for the user account you're working with. As a result, you will receive SID in the hexadecimal format.
- Now we will update our database table with newly obtained SID using:
UPDATE UserInfo SET tp_SystemID = 0x010500000000000000000986BD9EA976E44036C3F5D3F04040000
FROM UserInfo
WHERE tp_ID = '1' and tp_guid='XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' and
tp_Login='hostname\username' and tp_SiteID = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'
where tp_ID, tp_guid, tp_Login and tp_SiteID are the attributes of the user account for which we want to update tp_SystemID. Note: you might have to repeat those steps for the services accounts which are running your search, shared services provider, etc.
After implementing the steps above, you might have to update service accounts in the SharePoint configuration. Microsoft has an excellent guide on how to make those changes.
If you receive "No process is on the other end of pipe…" error when you attempt to connect to the SQL Server 2005, then check if:
- SQL Server services are running
- remote connections enabled
- port settings are correct
- DNS gets resolved properly
- firewall is not preventing you from connecting successfully
- and so on and so forth…
And, if after all of these checks the error is still displayed, then the bad certificate could the one to blame. In order to be successfully loaded by SQL Server 2005, a certificate must meet all the following requirements:
- The certificate must be in either local machine or current user certificate store
- The certificate must have a good time stamp, i.e. the current system time must be in the valid time window of the certificate
- The certificate must be meant for Server Authentication
- The Certificate's key spec must include AT_KEYEXCHANGE property.
- The certificate's subject CN must match the FQDN of the server machine or the FQDN of the virtual server if the server runs on failover cluster.
If for some reason the server cannot find a valid certificate, then the server will issue a self-signed certificate to encrypt data communication between the server and client. The login packet will always be encrypted, while the data communication will be encrypted only if the server and the client are configured to do so. Anyway, to resolve the issue with the certificate, do the following:
- Open SQL Server Configuration Manager; under SQL Server Network Configuration right-click on Properties for your SQL instance, and click on Certificate tab. Then make sure that the correct and valid certificate is selected, and then reboot your SQL Server.
- If invalid certificate is explicitly specify, then remove that certificate
- Otherwise, Open Personal Store in Certificates (Local Machine and User) management console, backup all certificates in that folder and then remove all certificates from personal store
- Reboot your SQL Server
- To make sure that self-signed certificate has been loaded, open ERRORLOG file for your SQL Server and search for "A self-signed certificate was successfully loaded for encryption" line in the log file.
- Attempt to reconnect to the SQL Server
There appears to be a bug in SharePoint (WSS or MOSS) that breaks the backup process after extending a web application. If you need to add host headers or load balancing URL to your SharePoint web application, the only way to do that is to extend your web application (SharePoint Central Administration >> Application Management >> Create or extend Web application >> Extend an existing Web application). Even though extending web application allows you to add load balancing URL and host headers, for some reason, it breaks the backup for all site collections and sites that use that web application. You could still backup SharePoint site collection using stsadm.exe command (stsadm.exe –o backup –url http://damagedWebApp/ -filename backup_damaged_webapp.bak), but built-in SharePoint backup through Central Administration wouldn't work. You will start receiving an error (Error: Object SharePoint - 80 failed in event OnPrepareBackup. For more information, see the error log located in the backup directory. KeyNotFoundException: The given key was not present in the dictionary) every time you attempt to backup modified web application using Central Administration website.
To get backup process working again you will have to perform the following steps:
- Backup your web application database through Microsoft SQL Server Management Studio!!!
-
Detach the content database from the web application:
- Go to SharePoint Central Administration >> Application Management >> Content Databases
- Make sure to select damaged web application form the drop down list, otherwise you will messing with the wrong web application
- Click on the content database used by damaged web application
- Check "Remove content database" and click OK (ignore the warning). Removing the content database does not delete the database; it only removes the association of the database with the Web application. This action is analogous to detaching a database in SQL Server in that the content of the database remains intact.
-
Delete the web application (remove SharePoint from it):
- Go to SharePoint Central Administration >> Application Management >> Delete Web Application
- Make sure to select damaged web application form the drop down list
- Leave "Delete Content Databases" and "Delete IIS Websites" options set to "No". You can always remove them later, right now we are only interested in removing SharePoint references to the damaged web application
-
Create a new web application with the appropriate settings for host header, load balancing URL and so on:
- Go to SharePoint Central Administration >> Application Management >> Create or extend Web application >> Create new Web application
- Configure new web application settings. Don't forget host headers, new website and port settings, and load balancing URL (as needed) and click OK
- Remove content database of the newly created web application by running
stsadm.exe -o deletecontentdb -url http://newwebapplication:port -databasename NEW_WEBAPP_DB
Go to http://technet.microsoft.com/en-us/library/cc262449.aspx for more information on deletecontentdb operation of stsadm.exe command
- Attach the existing database to the new web application by running
stsadm.exe -o addcontentdb -url http://newwebapplication:port -databasename OLD_WEBAPP_DB
Go to http://technet.microsoft.com/en-us/library/cc263422.aspx for more information on addcontentdb operation of stsadm.exe command
- Make sure that host headers in IIS as well as Alternate Access Mapping is set properly and try running the full SharePoint backup again through Central Administration.
After those steps you will probably also have to configure search server setting for your content database (SharePoint Central Administration >> Application Management >> Content Databases >> Select Search Server from drop-down list) and get full crawl running to make sure that your search is up-to-date and working properly.
Now that your backup is running again, it should safe to remove:
- the database that was created with new web application (see Step 5 above)
- the website used by damaged web application (assuming you don't need this website for anything else)
Related resources:
Before I start writing how to set up Kerberos authentication in SharePoint, let me explain our set up a little bit, i.e. server names, account names and so on that will be used in this guide:
WSSSERVER1 – SharePoint web front server
WSSSERVER2 – another web front server (optional)
DBSERVER1 – database server running Microsoft SQL Server 2005 SP2
Domain\wss_srvc_account – User account used to run SharePoint services
Domain\sql_srvc_account – User account used to run SQL services on database server
Domain\wss_apppool – User account used to run SharePoint web application pool
Domain\mysite_apppool – User account used to run My Site web application pool
Domain\sspadmin_apppool – User account used to run Shared Services Provider web application pool
To set up a Kerberos authentication in SharePoint (WSS or MOSS) you need to do a bunch of small configuration changes:
- Make sure that you have host headers set up for your SharePoint sites. For example, in case of Windows SharePoint Services you will have only the main SharePoint website, whereas in case of Microsoft Office SharePoint Server 2007 you will have main SharePoint website, My Site website, and Shared Services Provider Sites. For the sake of simplicity, let's call those host headers: http://sharepoint, http://mysite, http://sspadmin respectively.
-
Update Alternate Access Mappings to point websites to website host headers. In other words, replace http://servername:2222 entry with
http://sharepoint
-
Add SPN records for:
- Hostnames and FQDN of computer account(s) of your SharePoint server(s), for example
Setspn.exe -A HTTP/WSSSERVER1 DOMAIN\wss_ srvc_account
Setspn.exe -A HTTP/WSSSERVER1.domain.local DOMAIN\wss_srvc_account
Setspn.exe -A HTTP/WSSSERVER2 DOMAIN\wss_ srvc_account
Setspn.exe -A HTTP/WSSSERVER2.domain.local DOMAIN\wss_srvc_account
- Hostnames and FQDN of computer account of your SQL server, for example
Setspn.exe -A MSSQLSvc /DBSERVER1:1433 DOMAIN\sql_srvc_account
Setspn.exe -A MSSQLSvc /DBSERVER1.domain.local:1433 DOMAIN\ sql_srvc_account
-
Host headers for your SharePoint websites, for example
Setspn.exe -A HTTP/INTRANET DOMAIN\wss_apppool
Setspn.exe -A HTTP/ INTRANET. domain.local DOMAIN\wss_apppool
Setspn.exe -A HTTP/MYSITE DOMAIN\mysite_apppool (Do not apply in case of WSS)
Setspn.exe -A HTTP/MYSITE. domain.local DOMAIN\mysite_apppool (Do not apply in case of WSS)
Setspn.exe -A HTTP/SSPADMIN DOMAIN\sspadmin_apppool (Do not apply in case of WSS)
Setspn.exe -A HTTP/SSPADMIN. domain.local DOMAIN\sspadmin_apppool (Do not apply in case of WSS)
-
Configure "Trust for Delegation" on all computer accounts and user accounts used in SharePoint configuration. To configure "Trust for delegation":
- Open Active Directory Users and Computers management console
- Right click on a user or computer account that require ""Trust for Delegation" configured and click on Properties
-
Where you find this option in the GUI depends on the Active Directory functional level. In case of Windows 2000 domain, the option is under Account tab for user accounts and General tab for computer accounts. In case of Windows 2003 domain, the option is under a separate Delegation tab. Note: Delegation tab is only visible for accounts that have SPNs registered
-
Configure Component Services to allow Local Launch and Activation permissions for IIS WAMREG Admin Service for all application pool accounts used in SharePoint configuration. To configure Component Services setting go Control Panel >> Component Services >> Computers >> My Computer >> DCOM Config >> properties of the "IIS WAMReg Admin Service" >> Security tab >> edit "Launch and Activate Permissions" >> add "Local Launch" and "Local Activation" permissions for all the application pool accounts
-
Because in IIS7 HTTP.sys is handling the authentication, it is by default done under the LocalSystem account regardless of the application pool account you're using. However, because even a single SharePoint server configuration is now considered a web farm, we should use a domain account to run SharePoint application pools. As a result we need to modify applicationhost.config file to configure the useAppPoolCredentials attribute in system.webServer/security/authentication/Windows-Authentication configuration section to true.
<windowsAuthentication enabled="true" useKernelMode="true" useAppPoolCredentials="true"/>
-
Once you have made a change to applicationhost.config file you might start getting errors 6398, 7076, 6482 in your Event Viewer. To get rid of those errors you need to apply hotfix KB946517. This hotfix is currently is available for Windows Server 2003, XP and Vista and it is under development for Windows Server 2008. I have used Vista version of the hotfix on Windows Server 2008 and it seems to have worked fine (knocking on wood...)
-
Now you're ready to switch your SharePoint web applications to Kerberos authentication. Open SharePoint Central Administration >> Application Management >> Authentication Providers >> choose your web application >> change authentication to Negotiate (Kerberos)
- In case of MOSS, to change your Shared Services Provider web application to use Kerberos authentication run the command: stsadm.exe -o SetSharedWebServiceAuthn –negotiate. Stsadm.exe is usually placed under C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\bin.
That's it, you should now have your web applications using more secure Kerberos authentication. You can use Fiddler (http://www.fiddlertool.com/Fiddler2/version.asp) to verify that your web application is in fact using Kerberos authentication. Here are a few links that will help you might find useful:
Related resources:
Recently I tried to install SQL Server 2005 on Windows Vista and I have received a warning message for IIS Feature requirement on the System Configuration Check page of the SQL Server 2005 Setup program. This means that you won’t be able to install SQL Server components, requiring IIS, such as Reporting Services. The problems occurs because IIS 7.0 is much more customizable and has much more components, so some of the components required for RS installation are missing from the default IIS 7.0 install. Here is the list of the IIS components required for Reporting Services:
|
Component |
Folder |
|
Static Content |
Common HTTP Features |
|
Default Document |
Common HTTP Features |
|
HTTP Redirection |
Common HTTP Features |
|
Directory Browsing |
Common HTTP Features |
|
ASP.Net |
Application Development |
|
ISAPI Extension |
Application Development |
|
ISAPI Filters |
Application Development |
|
Windows Authentication |
Security |
|
IIS Metabase |
Management Tools |
|
IIS 6 WMI |
Management Tools |
For instructions on how to install missing IIS components in Windows Vista, go to: http://www.iis.net/default.aspx?tabid=2&subtabid=25&i=957 (http://www.iis.net/default.aspx?tabid=2&subtabid=25&i=957).
For instructions on how to install missing IIS components in a Server Core installation of Windows Server 2008, go to: http://www.iis.net/default.aspx?tabid=2&subtabid=25&i=956
The solution is also explained in Microsoft KB920201.
If you have recently moved your SharePoint site (WSS 3.0 or MOSS 2007) from one hardware to another using backup/restore procedure, you might have noticed a few missing timer jobs definitions: Scheduled Approval, Scheduled Page Review, Scheduled Unpublish, Variations Propagate Page Job Definition, and Variations Propagate Site Job Definition. In our case, we had multilingual SharePoint site that stopped propagating variations after SharePoint move, so the content created in one language was not re-created in another. Very frustrating…
After looking into the problem, I discovered that SharePoint does not restore those timer job definitions by default. Naturally, not having those jobs will cause problems with scheduled publishing as well as variations propagation. To get those timer job definitions back on the list and running again, you will have cheat a little by creating a new site collection using Publishing Portal or Collaboration Portal templates. Creating new site collection using Publishing Portal or Collaboration Portal templates will force SharePoint to create missing timer job definitions. New site collection can be removed shortly after created.
Note: Changes made in variations since the SharePoint move might take while to propagate. To expedite the propagation you can manually force the variations update.