Archive for the ‘Windows Server 2008 R2’ Category

SSRS: Format number with 2 decimals points

=Format(Sum(Fields!TOTALHABERES.Value, "Detalle"),"#,##0.00")

How to do number formatting to 1 decimal point in SSRS?
SSRS Expressions Tips and Tricks
Formatting Numbers and Dates

Anuncios

Recover SQL Server sa password

Response on StackOverflow.com

You may have forgotten “sa” password, you never got it, the provider didn’t give it to you, whatever.

But, You have Administrator privileges on Windows.

The steps are the following:

  1. Go to SQL Server Configuration Manager.
  2. Select SQL Server and STOP the service.
  3. Right click on it, go to Properties.
  4. On “Startup parameters”, add the following at the beginning “-m;”
  5. Start SQL Server service.
  6. Now, you’re on Single User Mode.
  7. Open a Command window (on Win7 make sure to Run as Administrator)
  8. Type “SQLCMD” and hi ENTER.
  9. Now you’re logged on SQL Server in Single User Mode, without any password.
  10. Type: “CREATE LOGIN mynewuser WITH PASSWORD = ‘mynewpassword′;”
  11. Type: “GO;”
  12. Type: “sp_addsrvrolemember ‘mynewuser’, ‘sysadmin’;”
  13. Type: “GO;”
  14. Stop SQL Server service.
  15. Right click it, go to Properties.
  16. On “Startup parameters”, remove the following from the beginning “-m;”
  17. Start SQL Server service.
  18. Open SQL Server Management Studio, login as “mynewuser” user.
  19. Go to Security, Logins, and change “sa” user password.

References:

How to recover SA password on Microsoft SQL Server 2008 R2
Disaster Recovery: How to regain your lost sysadmin access
Starting SQL Server in Single-User Mode
Tips & Tricks: YOU HAVE LOST ACCESS TO SQL SERVER. NOW WHAT?
sqlcmd Utility

Repeat page headers in SSRS 2008 R2

  1. Select your Tablix.
  2. On the panel below, you will see Row and Columns Groups.
  3. On the upper right corner, click and Select “Advanced Mode”.
  4. Now, on Row Groups, click on the first item “(Static”).
  5. On its Properties, set “Keep with group” to TRUE and “Repeat on new page” to TRUE.

Done!

Further reading:

Katmai Reporting Services 2008 Tablix control Repeat Column Headers does not work

How to: Display Row and Column Headers on Multiple Pages (Report Builder 3.0 and SSRS)

Controlling Row and Column Headings (Report Builder 3.0 and SSRS)

I want to Repeat Page Header on each Page for reports.Repeat on NewPage is not appearing in properties of Tablix member in SSRS 2008

Repeating TABLE Header on each page of the report when you print the exported to excel report

Repeating Page Titles in SSRS 2008 R2

Tablix headers not repeating in SSRS 2008

Repeat Header / Keep Header Visible in Tables in RS 2008

Consume SSRS 2008R2 x64 report from ASP.NET app using Oracle ODAC11.2.0.1.0 on IIS 7.5 (Enabled 32 bit apps) on Win7 x64

On my development machine (Win7 x64, IIS 7.5, SSRS 2008R2 x64), I have an ASP.Net app (application pool with “Enable 32-bit apps” set to “True”) connecting to an Oracle database server, using Oracle Data Provider for .Net 32 bits (ODAC 11.2.0.1.0, Oracle.DataAccess 2.112.1.0.). IIS 7.5 and SSRS 2008 R2 run on the same development machine. Oracle runs on a test server.

It works fine, except when I embed a report from SSRS 2008 R2, using ReportViewer, like this:

    reportViewer.ProcessingMode = ProcessingMode.Remote     reportViewer.ServerReport.ReportServerCredentials = New ReportServerCredentials()     Dim serverReport As ServerReport     serverReport = reportViewer.ServerReport     serverReport.ReportPath = "/" + ReportServerCredentials.ReportFolder + "/rptParteIngreso"     serverReport.ReportServerUrl = New Uri(ReportServerCredentials.urlServerReporting)     Dim parameters() As ReportParameter = {P_IDPAIS, P_FECHAINICIO, P_FECHAFIN, P_NROPARTE, P_FLAGAPROBING}     Try         serverReport.SetParameters(parameters)     Catch ex As Exception         MessageBoxs.Show(ex.InnerException.ToString())     End Try 

It throws the following error:

Microsoft.Reporting.WebForms.ReportServerException was unhandled by user code   ErrorCode=rsProcessingAborted   Message=Error al procesar el informe. (rsProcessingAborted)   Source=Microsoft.ReportViewer.WebForms   StackTrace:        en Microsoft.Reporting.WebForms.ServerReportSoapProxy.OnSoapException(SoapException e)        en Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.ProxyMethodInvocation.Execute[TReturn](RSExecutionConnection connection, ProxyMethod`1 initialMethod, ProxyMethod`1 retryMethod)        en Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.SetExecutionParameters(ParameterValue[] Parameters, String ParameterLanguage)        en Microsoft.Reporting.WebForms.ServerReport.SetParameters(IEnumerable`1 parameters) .....        en System.Web.UI.Control.OnLoad(EventArgs e)        en System.Web.UI.Control.LoadRecursive()        en System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)        ErrorCode=rsErrorOpeningConnection        Message=Cannot create a connection to data source 'DSRRHH'. (rsErrorOpeningConnection)        InnerException: Microsoft.Reporting.WebForms.ReportServerException             Message=El intento de cargar las bibliotecas de clientes de Oracle lanzó BadImageFormatException. **Este problema ocurrirá al ejecutar el modo 64 bits con los componente cliente de Oracle de 32 bits instalados**.             InnerException: Microsoft.Reporting.WebForms.ReportServerException                  Message=Se ha intentado cargar un programa con un formato incorrecto. (Excepción de HRESULT: 0x8007000B) 

The relevant message I think is “This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.

Also, on the Task Manager, I’ve checked Reporting Services is running as a 64-bit process(ReportingServicesService.exe) and my app is running on 32 bits (w3wp.exe *32).

However, when designing the report, it works OK, using “.Net Framework Data Provider for Oracle”, which I think is provided by Microsoft.

So, my conclusion is there’s a conflict between my ASP.Net app (running as 32-bit) and my reports published on a SSRS 2008 R2 x64 server.

So, my question is, how can I embed those SSRS reports in my ASP.Net app?

Some alternatives I’ve thought:

  1. Diable “Enable 32-bit apps” on application pool on IIS, so it will run as 64-bits.
  2. Change SSRS process to run as 32-bits. (Is this even posible?)

Also, I’ve published the reports to another server (SSRS 2008 R2 x64 on Win 2008 R2 x64) and I can embed them without any problem from my ASP.Net 32-bits app on my development machine.

Any help would be greatly appreciated.

 

UPDATE:

Solved. Installed Oracle Data Provider for .Net 64 bits (ODAC 11.2.0.1.0, Oracle.DataAccess 2.112.1.0.) and disabled “Enable 32-bit apps” on application pool on IIS, so it will run as 64-bits. This way, it can connects to SSRS 2008 R2 64 bits.

SQL 2008R2 Linked Servers

I want to connect Server A with Server B, so I can query a table on a database on Server B from a database on Server A. This is called a “distributed query”

  1. Login as “sa” into SQL Server on Server A.
  2. Go to Server Objects/Linked Servers.
  3. Right click, New Linked Server.
  4. Note: If linked server is of type SQL Server, then the linked server name is also the network name of the server.
  5. Enter Linked server name: “Server B” network name (or IP address). If your SQL Server is a named instance, it would be “SERVER_NAME\INSTANCE_NAME” or “SERVER_IP\INSTANCE_NAME”
  6. Choose Server type: SQL Server.
  7. Go to Security.
  8. Click Add.
  9. Local login: sa
  10. Impersonate: not checked
  11. Remote user: sa
  12. Remote password: sa password on remote server
  13. Click OK

Now you can write a distributed sql query from a database on Server A, like this:

select * from [linked_server_name].db_name.schema_name.table_name

However, the setup describe above is not the most secure. Use of  “sa” login shoul be disabled on both servers. It’s recommended that you create a login for each database, with the minimum permissions required, and use this login as remote user (11) and remote password (12) described above.

Of course, you can also do this using a SQL script (sp_addlinkedserver)

References:

Linking Servers

Accessing External Data

Identifying a Data Source by Using a Linked Server Name

sp_addlinkedserver (Transact-SQL)

SQL Server: disable Windows authentication

Actually, this is not completely possible.

There are two authentication modes for SQL Server (2008R2):

  1. Windows Authentication: stronger security, Kerberos protocol.
  2. Mixed Mode (Windows and SQL Server): use of “sa” login. Less secure. Best option for web applications.

I’ve got a web server (IIS) andwhen I log into Windows (2008R2), if I open SQL Server, Windows Authentication is selected by default and it lets me access without any password.

This is because, by default, the Windows administrators group is assigned the “sysadmin” server role. We can remove them from this server role or remove completely the group from the SQL Server logins.

So, I did the following:

  1. Access SQL Server as “sa” user.
  2. Go to Security/Logins
  3. Right click on the BUILTIN\Administrators login or PC-NAME\USER-NAME login.
  4. Choose Properties
  5. Go to Server Roles
  6. Uncheck “sysadmin” role
  7. Make sure it has only the “public” role selected.
  8. Click OK

This way, Windows administrator can access SQL Server but without permissions to modify anything.

However, “sa” user can still access SQL Server with full privileges.

Of course, it’s not recommended to use “sa” login for any application (ASP.Net).

You better create a specific login for each application, with just the needed permissions to access its database.

References:

How to Disable Windows Authentication

Authentication in SQL Server (ADO.NET)

SQL SERVER – Disable Windows Authentication – Remove Windows Authentication Login Account

 

Powershell: compress backups and FTP transfer

I use this snippet to check my database backups folder for backup files not compressed yet, compress them using 7-Zip, and finally deleting the “*.bak” files to save some disk space. Notice files are ordered by lenght (smallest to biggest) before compression to avoid some files not being compressed.

$bkdir = "E:\BackupsPWS" #backups location directory
$7Zip = 'C:\"Program Files"\7-Zip\7z.exe' #compression utility
$files_to_transfer = New-Object System.Collections.ArrayList #list of zipped files to be transferred over FTP
$ftp_uri="myftpserver"
$user="myftpusername"
$pass="myftppassword"

#find .bak files not zipped yet, zip them, add them to the list to be transferrd
get-childitem -path $bkdir | Sort-Object length |
where { $_.extension -match ".(bak)" -and
-not (test-path ($_.fullname -replace "(bak)", "7z")) } |
foreach {
$zipfilename = ($_.fullname -replace "bak", "7z")
Invoke-Expression "$7Zip a $zipfilename $($_.FullName)"
$files_to_transfer.Add($zipfilename)
}

#find .bak files, if they've been zipped, delete the .bak file
get-childitem -path $bkdir |
where { $_.extension -match ".(bak)" -and
(test-path ($_.fullname -replace "(bak)", "7z")) } |
foreach { del $_.fullname }

#transfer each zipped file over FTP
foreach ($file in $files_to_transfer)
{
$webclient = New-Object System.Net.WebClient
$webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass) # FTP credentials
$ftp_urix = $ftp_uri + "/" + $file.Substring($bkdir.Length + 1) # ftp address where to transfer the file
$uri=[system.URI] $ftp_urix
$webclient.UploadFile($uri, $file) #transfer the file
}