SQL Server Ports
First published on TECHNET on Jul 03, 2012 Quick cheat sheet for port numbers used by SQL Server services or services that SQL Server may depend on:21TCPFTP (replication)80TCPHTTP endpoints, Reporting Services, HTTP replication135TCP & UDPRPC, WMI, MSDTC, SQL Agent file copy, and TSQL Debugger (RPC used for multiple purposes including SSIS and clustering.
How to create Linked server from SQL Server to Azure SQL Database
Here in this blog, we are going to demo how to configure Linked server from on-prem SQL Server instance to Azure SQL database. I will have a reference to this blog in my subsequent blogs that will soon be available for Dynamic Data Masking & Cross database/server queries. We have taken example of two databases, Database1 (Copy of AdventureWorks 2019) as Azure SQL database & Database2 hosted in On-prem SQL Server Instance. In this demo we will be querying Database1 tables in the context of Database2. Database1: Azure SQL database Database2: SQL Server Instance on-prem Here is the sequence of steps that you need to follow to configure Linked Server using SSMS tool. Step-1: Connect to SQL Server Instance in SSMS tool and go to Object Explorer. Expand the Server Objects, right click on Linked Server and create a New Linked Server. Step-2: Go to General tab in the new Linked Server window. Under the Server type section, choose the Other data source option. Give a suitable name to the Linked Server as per your choice in the Linked Server section. Choose the “Microsoft OLE DB Provider SQL Server” in the Provider dropdown. In the Data source section, specify the Azure database logical server name for e.g., logicalservername.database.windows.net. You just need to change the logicalservername to the actual Azure server name which you can get from the Azure portal. Enter the Azure database name that you want to create the linked server to in the catalog field. Step-3: Now go to Security tab and choose the option “Be made using this security context”. Enter the SQL login credentials which is already present on the Azure DB server and has access to Database1. Please note this option is the least secure way to address the security configuration of Linked Server as any user who uses the Linked Server will be authenticated on the remote server using credentials provided here. Use of this option should be limited to testing environment. Step-4: Once the Linked Server is successfully created you can see it when you expand Linked Server section in Object Explorer and expand it further to view the list of tables. Step-5: Open a new query window in SSMS and switch to Database2 context on the on-prem SQL Server. Run the SELECT query to fetch data using the Linked Server that you just created in the previous steps. select * from [AZURE DATABASE DDMTEST].[Database1].[Person].[PersonPhone] Hope you find the blog helpful. Please share your questions or feedback.Dynamic SQL & SQL injection
First published on MSDN on Jan 04, 2007 I know there are a lot of papers that talk about dynamic SQL in more depth than what I am going to cover, but as SQL injection is still one of the biggest security problems in the relation databases world, that I decided to include this part as a quick (and hopefully helpful) reminder.The Easiest Way To Rebuild The master Database
First published on TECHNET on Feb 01, 2011 If your SQL Server master database becomes corrupt, such as from its disks losing power while SQL Server was running, the conventional advice is to rebuild the master database, start SQL Server, then restore the backup of the master database.Ledger - Automatic digest upload for SQL Server without Azure connectivity
Ledger is a new feature that offers the power of blockchain in Azure SQL Database and SQL Server 2022. We’re making the data in the SQL database verifiable using the same cryptographic patterns seen in blockchain technology, while keeping the flexibility and performance of a traditional database. Ledger databases are centrally managed databases that can also cryptographically attest to other parties, such as auditors or other business parties. Ledger ensures your data can be trusted and hasn't been tampered with. How it works Each transaction that the database executes is cryptographically hashed (SHA-256). Transactions are cryptographically linked together, like a blockchain. Cryptographically hashed database digests represent the state of the database. They can be periodically generated and stored outside the database in a tamper-proof storage location, such as an Azure immutable Blob storage or Azure Confidential Ledger (only for Azure SQL Database). Database digests are later used to verify the integrity of the database by comparing the value of the hash in the digest against the calculated hashes in the database. How to configure automatic digest upload for SQL Server instances without Azure connectivity? While working with customers on ledger for the Early Adoption Program of SQL Server 2022, many of them had concerns about the automatic digest upload for on-premises SQL Server instances. Microsoft currently only supports Azure immutable blob storage for ledger, but many customers don't open internet connection or connection to Azure for their SQL Server due to internal security policies. The customers needed to write their own custom solution to manually generate the digests and store them securely on their on-premises Write Once Read Many (WORM) storage devices. To help our customers, we have created 2 scripts that could be used as a starting point and can be modified according to the customers’ needs. Scripts Generate manual digests with SQL Agent Job This job is going to execute the sp_generate_database_ledger_digest stored procedure on a regular basis. The schedule is currently set to 30 seconds, just like the automatic digest upload. The digests are always added to the same file. The stored procedure is executed in a PowerShell job step. Since PowerShell passes results as streams of objects, cmdlets can differentiate between data and messages, and only put data on the output pipeline. Execute the script below on your SQL Server 2022 instance to create the SQL Agent Job. Remark: Make sure you change: <--YourDatabase--> into the database name that you want to generate digests for e.g., ConstosoHR <--YourDigestFile--> into the file path where you want to store your digest file e.g., C:\Ledger\Digests.txt. This should be a location on the WORM device. USE [msdb] GO BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Generate Manual Digest', @enabled=0, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, _name=N'Generate Manual Digest', _id=1, @cmdexec_success_code=0, _success_action=1, _success_step_id=0, _fail_action=2, _fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'PowerShell', @command=N'Set-Location "SQLSERVER:\SQL\.\$(ESCAPE_NONE(SRVR))\Databases\<--YourDatabase-->" Invoke-Sqlcmd "SET NOCOUNT ON; EXEC sp_generate_database_ledger_digest" -Verbose | % { $_[0] } | out-file ''<--YourDigestFile-->'' -Append', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 30 seconds', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=2, @freq_subday_interval=30, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20220414, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959, @schedule_uid=N'0d1ace0f-c54d-4021-afbe-7e4817fac283' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, _name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO Run manual verification The stored procedure below fetches the digest file from a file location. This can be set with the parameter @FileLocation. This should be a location on the WORM device that you have used to store the digest file on. It reads all digests that were generated, creates a JSON array of the digests, and passes that to the stored procedure sp_verify_database_ledger to run the database verification. This new stored procedure could also be scheduled by a SQL Agent Job to run the verification on a regular basis. CREATE PROCEDURE sp_verify_manual_digest @FileLocation NVARCHAR(256) AS BEGIN SET NOCOUNT ON CREATE TABLE #ManualDigests( Content NVARCHAR(1000) ) Declare @Statement NVARCHAR(max) SET @Statement=' BULK INSERT #ManualDigests FROM ''' + @FileLocation + ''' WITH ( DATAFILETYPE = ''widechar'', ROWTERMINATOR =''\n'' );' EXECUTE SP_EXECUTESQL @Statement DECLARE @Digest NVARCHAR(MAX) SELECT @Digest='[' + STRING_AGG(Content, ',') + ']' FROM #ManualDigests EXECUTE sp_verify_database_ledger @Digest DROP TABLE #ManualDigests END GO
