Category Archives: msSQL

[How-To] Import large .sql file into MSSQL (Microsoft SQL) Server

Leave a reply

Objective: You have a .sql file you wish to import in Microsoft SQL Server (MSSQL)

Solution: Microsoft has a command line utility called Sqlcmd that can be used to import very large datasets into SQL server without having to open the file.  More information on this utility can be found here: http://msdn.microsoft.com/en-us/library/ms162773.aspx

Here is a snippit of the utility and its available switches before beginning:

C:\Users\Administrator>sqlcmd /?

usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f  | i:[,o:]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-K application intent]
  [-M multisubnet failover]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

Here is an example of how to use the utility:

  1. Open up a command prompt
    1. Note: Administrative privileges are optional depending on how you have your security settings configured.
      Server 2012 - Administrative Command Prompt
  2. Execute the following command
    1. sqlcmd -S SERVERNAME\INSTANCE_NAME -i C:\path\mysqlfile.sql
      1. Here are some notes/tricks you might want to use:
        • If you are using the default instance when you installed MSSQL server, you don't have to specify \INSTANCE_NAME
        • You can log results to a text file by appending the following switch: -o C:\path\results.txt
        • You can specify a database to import to by appending the following switch: -d MYDATABASE
        • You can specify username and password by appending the following switch: -u USERNAME -p PASSWORD
          --Note: You can wait to be prompted for credentials by optionally leaving out the -p switch, however if you are piping the results out to notepad, the command will not fully execute.

 

msSQL Server 2008 R2 - Restore Failed

9 Replies

Symptom: When restoring a database in Microsoft SQL Server Management Studio, you receive the following dialog box similar to something below:

Restore failed for Server 'myserver'. (Microsoft SqlServer.SmoExtended)

Additional Information
System.Data.SqlClient.SqlError: File 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mydatabase.mdf' is claimed by 'mydatabase_FG1'(3) and 'mydatabase'(1).  The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)

Solution: On the Restore Database - mydatabase window inside of Microsoft SQL Server Management Studio:

  1. Click on the Options page.
  2. Underneath Restore the database files as: label, make sure all of the "Restore As" values are unique.  In this case, I renamed each *.mdf file to it's "Original File Name" column value.
    1. Before
      1. Restore Database msSQL
    2. After
      1. Restore Database msSQL
  3. Click OK

Viola!  Your database should now successfully restore 🙂

Lync 2010 - Cannot impersonate user for data source 'CDRDB'. (rsErrorImpersonatingUser) error

3 Replies

Symptom: You receive the following error when browsing to the following page in the Reports Viewer: https://mymachine.mydomain/ReportServer/Pages/ReportViewer.aspx?%2fLyncServerReports%2fReports+Home+Page&rs:Command=Render  Alternatively, you receive this error when you go to https://mymachine.mydomain.com/Reports/ and click on LyncServerReports and then Reports Home Page.

Note: the solution below applies to the QMSDB as well.

  • An error has occurred during report processing. (rsProcessingAborted)
    • Cannot impersonate user for data source 'CDRDB'. (rsErrorImpersonatingUser)
      • Log on failed. Ensure the user name and password are correct. (rsLogonFailed)
        • For more information about this error navigate to the report server on the local server machine, or enable remote errors

Solution: For whatever reason, my service account I created actually had the incorrect password to login to the 'CDRDB' data source.  To fix/troubleshoot this, follow the steps below:

  • Head over to https://myserver.mydomain/Reports (myserver being the server with the reporting services on it)
  • Click on LyncServerReports
  • Click on Reports_Content
  • Click on CDRDB
  • Under Credentials stored securely in the report server, type in the username/password are want to use to connect to the SQL server, and then click the Test Connection button.  If you should see a Connection created successfully. message, you are good to go.  If you see a Log on failed. Ensure the user name and password are correct. message, ensure that the account you setup has the correct username/password in active directory, is Unlocked in Active Directory (due to bad password attempts), and that it has permissions to the databases mentioned in the Connection string on the same page.

SQL Server 2008 R2 - Updating a msSQL instance/server

1 Reply

Scenario: You have accidentally installed SQL Server 2008 R2 Workgroup and you need SQL Server 2008 R2 Standard.  Obviously, you don't want to rebuild your server from the ground up and have extended downtime, so an upgrade option would be sweet.  Luckily, Microsoft has come to the rescue with an easy way to update your msSQL server/instances.

Solution: Go to the Microsoft Licensing center or grab your SQL Server disk/installation media of the correct version.  I.e. if I was running standard, I would grab the enterprise disk to upgrade.  Once you have the disk, follow these steps:

  1. Find your installation media and double click on Setup.exe
  2. When the SQL Server Installation Center window comes up, click on Maintenance
  3. Click on Edition Upgrade
  4. On the SQL Server 2008 R2 Setup - Setup Support Rules window, click OK
  5. Click Next > on the Upgrade the Edition for SQL Server 2008 R2 window
  6. Click Next > on the Enter a product key: page.
  7. Click I accept the license terms. and then click Next >
  8. Select the instance of the SQL Server you wish to upgrade and then click Next >
  9. Click Next >
  10. Click Upgrade

To verify your SQL Server upgraded, follow this guide here to pull your msSQL version: http://jackstromberg.com/2013/01/how-do-i-find-out-if-my-sql-server-is-32-bit-or-64-bit/

How do I find out if my SQL server is 32-bit or 64-bit?

2 Replies

Want to know what version of SQL server your have running? All we need to do is execute one SQL command and away we go 🙂

  1. Go to your SQL server
  2. Open up the Microsoft SQL Server Management Studio
    1. Start->All programs->Microsoft SQL Server 2008 R2->SQL Server Management Studio
  3. Login with your user
  4. Click the New Query button
  5. Execute the following query
    1. SELECT SERVERPROPERTY('edition')

SQL Server 2008 R2 - Reporting Services Configuration Manager - The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

Leave a reply

Symptom: When installing the Monitoring Agent for Lync 2010, I was receiving the following information in the "log" file:

> Deploying Monitoring Server Reports... This might take a few minutes.
The Monitoring Server is using SQL instance "myserver.mydomain".
The data source is using SQL instance "(local)".
The following URL will be used for deployment: https://myserver.mydomain:443/ReportServer
SQL Server logon credentials for "mydomain\myuser" already exist. Use the existing logon credentials.
"[QoEMetrics]" role "[ReportsReadOnlyRole]" has already assigned to "mydomain\myuser".
"[LcsCDR]" role "[ReportsReadOnlyRole]" has already assigned to "mydomain\myuser".
Start to deploy reports...
The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.
An error occurred when deploying Monitoring Server Reports. For details, see the log.

Turns out this was because I had accidentally forgot to add in the common name to my SSL certificate as a SAN address.  Bottom line, the issue is that your SSL certificate is bad.

Solution:

  1. Request a new SSL certificate
    1. Start->mmc
    2. Add in the Certificates snap-in
    3. Select Local Computer
    4. Expand Certificates->Personal->Certificates
    5. Right click, All Tasks->Request new Certificate...
    6. Next
    7. Next
    8. Select your policy
    9. Enter in the information you would like.  Note, if you are adding a SAN address for the short name, make sure you add both the FQDN and the short name as DNS entries in the Alternative name box.
    10. Click on the Private Key tab, expand the little arrow and check Make private key exportable
    11. Click OK
    12. Click Enroll
  2. Click Start->All Programs->Microsoft SQL Server 2008 R2->Configuration Tools->Reporting Services Configuration Manager.
  3. Click Connect
  4. Click Web Service URL
  5. Under SSL Certificate, select the appropriate certificate and click Apply
  6. At this point, you either saw a bunch of green check marks and you are good to go, or else you probably hit the dreaded "rouge SSL cert" error.  To fix that, please see this link: http://jackstromberg.com/2013/01/sql-server-2008-r2-reporting-services-configuration-manager-create-certificate-binding-failed-hresult-0x80040238/

SQL Server 2008 R2 - Reporting Services Configuration Manager - Create certificate binding failed - HRESULT: 0x80040238

3 Replies

Symptom: When changing an SSL certificate inside of the SQL Server 2008 R2 Reporting Services Configuration Manager, you receive the following error:

Create certificate binding.

When you click on "Tell me more about the problem and how to resovle it." you receive the following:

Microsoft.ReportingServices.WmiProvider.WMIProviderException: An SSL binding already exists for the specified IP address and port combination. The existing binding uses a different certificate from the current request. Only one certificate can be used for each IP address and port combination. To correct the problem, either use the same certificate as the existing binding, or remove the existing SSL binding and create a new binding using the certificate of the current request.

---> System.Runtime.InteropServices.COMException (0x80040238): Exception from HRESULT: 0x80040238
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)
at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.CreateSSLCertificateBinding(String application, String certificateHash, String ipAddress, Int32 port)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.CreateSSLCertificateBinding(UrlApplication app, String certificateHash, String ipAddress, Int32 port)

This error really sucks and the reason behind it is that Microsoft just didn't do a good job removing/unbinding SSL certificates from an interface.  Luckily, I have the solution to get you up and going...

Solution:

  1. Download the Windows Server 2003 Support Tools from Microsoft
    1. http://www.microsoft.com/en-us/download/details.aspx?id=7911
  2. Install the tools on your local machine or on the server.  You may get a warning about incompatibility if you install it on your Windows 7 machine.  I ignored this and things seem to work fine 😛  Just note if you do this, you will need to copy the following folder from your local machine to the server with SQL Server Reporting Services Configuration Manager: C:\Program Files (x86)\Support Tools
  3. Open up a command prompt with Administrator privileges on the server with SQL Server 2008 R2 Reporting Services Configuration Manager
  4. Navigate to the following directory (if you copied the folder from your local machine, browse to the appropriate directory you copied the support tools to):
    1. cd "C:\Program Files (x86)\Support Tools"
  5. Launch SQL Server 2008 R2 Reporting Services Configuration Manager
    1. Start->All Programs->Microsoft SQL Server 2008 R2->Configuration Tools->
      Reporting Services Configuration Manager
  6. Click on Web Service URL
  7. Click on the Advanced... button
  8. Remove any items listed inside of the "Multiple SSL Identities for the Report Server Web Service" box.
  9. Click OK
  10. Go back to your command prompt with the Administrator privileges and execute the following commands to unbind the old SSL certificate
    1. netsh http delete sslcert ipport=[::]:443
      1. You should see something like "SSL Certificate successfully deleted" -- If not, that is fine
    2. httpcfg delete ssl /i 0.0.0.0:443
      1. You should see something like "HttpDeleteServiceConfiguration completed with 0." -- If not, that is fine as long as the command above said it removed a certificate
  11. Go back to the Reporting Services Configuration Manager and select your SSL certificate in the  SSL Certificate dropdown.
  12. Click Apply

At this point, your certificate should have bound to the interface successfully.

Hope this helps someone!

How do I setup msSQL to use SSL?

Leave a reply

Want to encrypt your msSQL traffic?  Here is how to do it.

  1. Request/Install a certificate in the Windows Certificate store
    1. If you are on a domain with a certificate authority, you can do this by clicking Start->run->mmc
    2. Click File->Add/Remove Snap-in
    3. Select Certificates, click the Add button, select Computer account, click OK, click Finish the wizard.
    4. Expand Certificates (Local Computer) and navigate to Personal->Certificates
    5. Right click All Tasks -> Import... or Request New Certificate (depending on what you want to do)
  2. Once you have finished installing the certificate, click Start->All Programs->Microsoft SQL Server 2008 R2->Configuration Tools->SQL Server Configuration Manager (Launch SQL Server Configuration Manager)
  3. Expand SQL Server Network Configuration
  4. Right click on "Protocols for MSSQLSERVER" (or whatever your instance name is on the left side) and click Properties
  5. On the Flags tab, you can optionally set "Force Encyrption" to Yes, which will make your msSQL server only allow connections that are secure.  You may skip this step if you don't want to do this.
  6. Click on the Certificate tab.
  7. Select your certificate that you installed in Step 1 in the Certificate dropdown box.
  8. Click OK
  9. Click on SQL Server Services
  10. Right click on the SQL Server (MSSQLSERVER) service and click Restart (MSSQLSERVER==your instance name)

That's all that's to it.  Note, if you receive an error that the service cannot run (I forgot what the original error was), try disabling the VIA Client Protocol underneath SQL Native Client 10.0 Configuration inside of SQL Server Configuration Manager.

As a reference, you can find the official Microsoft KB article on how to do this here.