Tag: SQL Server

Cannot connect to MSSQL database from .NET 6 application

If you encounter SSL error when a .NET 6 application tries to connect to the MSSQL database

Microsoft.Data.SqlClient.SqlException : A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.)
System.ComponentModel.Win32Exception : The certificate chain was issued by an authority that is not trusted.

Not sure why, it might be that the new libraries are more strict regarding security. A workaround is to add TrustServerCertificate=True to the connection string.

Cannot restore database to SQL Server running in Docker container

I faced this issue, turned out when using docker cp to copy files to the container, the files were owned by root user and other users didn’t have read permission. This is because the files were copied from shared folder in Windows host to the linux VM.

Because only root can read, I couldn’t restore the DB using the copied file as the server cannot read the file.

Doing a chmod to add read permission before copying into the container solves the issue.

Hibernate mapping for decimal scale is wrong

When mapping a SQL table to a model using Hibernate or NHibernate, if you configure the mapping like following

<property name=”MyDecCol” type=”Decimal” precision=”28″ scale=”15″>
<column name=”MyDecCol” sql-type=”decimal” not-null=”false” />
</property>

For some reason, Hibernate/NHibernate will treat the field as a decimal with scale = 5 (5 decimal digits on the right side of the decimal separator). So if you save a value to this column with more decimal digits, it will be truncated to only 5 decimal digits.

I’m not sure why, but if you move the attributes to the inner column element, Hibernate and NHibernate will map the values correctly.

<property name=”MyDecCol” type=”Decimal”>
<column name=”MyDecCol” sql-type=”decimal” not-null=”false” precision=”28″ scale=”15″ />
</property>

Cannot connect to SQL server using SQL server authentication

The server may have been configured for integrated security only.

Change it to allow both SQL authentication and Windows Authentication by logging in using Windows user, then right click the server => Properties => Security, then change authentication mode to allow SQL authentication.

Then restart SQL server services.

Source: https://dba.stackexchange.com/questions/87390/sql-server-authentication-login-failed

Cannot find or connect to SQL instance showing when connecting to SQL server from SSMS

Checklist

-SQL Browser service is running on the DB server, this one listens to UDP broadcast to tell clients of its presence.

-UDP 1434 is allowed in the firewall of the server(inbound rule), this is so that UDP broadcasts from clients can reach server.

-TCP 1433 and 1434 are allowed in the firewall(inbound rule), this is so that SSMS can connect to SQL server instance.

-TCP and UDP protocols are enabled in SQL Configuration Manager.

-The SQL instance allows remote connections, check this by opening SSMS inside the server, connect to local instance, right click, choose Properties and check the option to allow remote connections.

-There is no IP conflict in the network with another server (if using Virtual machines, check that MAC addresses are different to have different IPs, otherwise SSMS may try to connect to wrong server).

Tips to generate a large SQL Server table for testing

To generate a large table with millions of records, it’s possible to use the following script:

declare @RecordsNum int = 10000000-(select count(*) from Person)

declare @Count int = 0

set nocount on

Begin transaction

while(@Count < @RecordsNum)

begin

insert into MyTable(Name, Address, Age)

values(substring(Convert(varchar(255), NewID()), 0, 30),

substring(Convert(varchar(255), NewID()),0 ,30), RAND(100) * 100)

set @Count = @Count + 1

End

commit

select count(*) from MyTable

Some things to do to improve performance of the script:

  • Set NoCount to ON
  • enclose the while loop in a transaction explicitly

Ideas were found in this page: http://mitchelsellers.com/blogs/2008/09/12/creating-random-sql-server-test-data.aspx

Find /bin folder for SSRS integrated with SharePoint

SharePoint strikes again.

Follow the article http://blogs.catapultsystems.com/aroney/archive/2013/02/11/custom-dlls-with-sql-server-reporting-services-in-sharepoint-integrated-mode/

Normally, you can find the SSRS at C:\Program Files\Microsoft SQL Server\ MSRS10_50.SQLSERVER\Reporting Services\

If you want to find the folder for SSRS integrated with SharePoint, it’s not where it’s usually located but under the /15 hive, C:\Program Files\Common Files\Microsoft Shared\Web Service Extensions\15\Webservices\reporting\bin