Here is the link as of writing time, and follow it without difficulty.
[oracle@hmc-P55A-UD3 bin]$ sudo su -
[sudo] password for oracle:
Last login: Sat Aug 19 10:48:19 EDT 2017 on pts/1
[root@hmc-P55A-UD3 ~]# curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 220 100 220 0 0 445 0 --:--:-- --:--:-- --:--:-- 446
[root@hmc-P55A-UD3 ~]# ls -lrt /etc/yum.repos.d/
total 52
-rw-r--r--. 1 root root 535 Oct 14 2015 CentOS-OpenStack-kilo.repo
-rw-r--r--. 1 root root 2893 Nov 29 2016 CentOS-Vault.repo
-rw-r--r--. 1 root root 1331 Nov 29 2016 CentOS-Sources.repo
-rw-r--r--. 1 root root 630 Nov 29 2016 CentOS-Media.repo
-rw-r--r--. 1 root root 314 Nov 29 2016 CentOS-fasttrack.repo
-rw-r--r--. 1 root root 649 Nov 29 2016 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root 1309 Nov 29 2016 CentOS-CR.repo
-rw-r--r--. 1 root root 1664 Nov 29 2016 CentOS-Base.repo
-rw-r--r--. 1 root root 1050 Jun 24 11:04 epel-testing.repo
-rw-r--r--. 1 root root 951 Jun 24 11:04 epel.repo
-rw-r--r--. 1 root root 2142 Jul 23 15:07 elrepo.repo
-rw-r--r--. 1 root root 193 Aug 19 10:49 msprod.repo
-rw-r--r--. 1 root root 220 Aug 19 12:07 mssql-server.repo
[root@hmc-P55A-UD3 ~]# yum update
Loaded plugins: fastestmirror, langpacks
packages-microsoft-com-mssql-server | 2.9 kB 00:00
packages-microsoft-com-mssql-server/primary_db | 12 kB 00:00
Loading mirror speeds from cached hostfile
* base: mirror.qoxy.com
* elrepo: mirrors.thzhost.com
* epel: 0xa028cd70.xtremenitro.org
* extras: mirror.qoxy.com
* updates: mirror.qoxy.com
No packages marked for update
[root@hmc-P55A-UD3 ~]# yum install -y mssql-server
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirror.qoxy.com
* elrepo: elrepo.mirror.angkasa.id
* epel: epel.mirror.angkasa.id
* extras: mirror.qoxy.com
* updates: mirror.qoxy.com
Resolving Dependencies
--> Running transaction check
---> Package mssql-server.x86_64 0:14.0.900.75-1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
mssql-server x86_64 14.0.900.75-1 packages-microsoft-com-mssql-server 165 M
Transaction Summary
================================================================================
Install 1 Package
Total download size: 165 M
Installed size: 165 M
Downloading packages:
mssql-server-14.0.900.75-1.x86_64.rpm | 165 MB 00:10
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mssql-server-14.0.900.75-1.x86_64 1/1
+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+
Verifying : mssql-server-14.0.900.75-1.x86_64 1/1
Installed:
mssql-server.x86_64 0:14.0.900.75-1
Complete!
[root@hmc-P55A-UD3 ~]# /opt/mssql/bin/mssql-conf setup
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=852741&clcid=0x409
The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409
Do you accept the license terms? [Yes/No]:Yes
Choose an edition of SQL Server:
1) Evaluation (free, no production use rights, 180-day limit)
2) Developer (free, no production use rights)
3) Express (free)
4) Web (PAID)
5) Standard (PAID)
6) Enterprise (PAID)
7) I bought a license through a retail sales channel and have a product key to enter.
Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409
Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.
Enter your edition(1-7): 2
Enter the SQL Server system administrator password:
The specified password does not meet SQL Server password policy requirements because it is too short. The password must be at least 8 characters
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...
This is an evaluation version. There are [157] days left in the evaluation period.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.
As I have disabled the firewalld in my desktop and installed command line tools in previous blog, I can directly connect locally.
[oracle@hmc-P55A-UD3 bin]$ sqlcmd -S localhost -U sa
Password:
1> create database testdb;
2> go
1> select name from sys.databases;
2> go
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
testdb
(5 rows affected)
Showing posts with label sqlserver. Show all posts
Showing posts with label sqlserver. Show all posts
Sunday, August 20, 2017
Install sqlcmd and bcp the SQL Server command-line tools on Linux (centos 7.3)
Followed
[oracle@hmc-P55A-UD3 bin]$ ls -l
[oracle@hmc-P55A-UD3 ~]$ sqlcmd -U perfstat -S 192.168.0.181,1433
Install sqlcmd and bcp the SQL Server command-line tools on Linux
tested okay from centos7.3 to sql server express 2014 running on windows 10.
[oracle@hmc-P55A-UD3 bin]$ ls -l
total 752
-rwxr-xr-x. 1 root root 239320 May 9 15:09 bcp
-rwxr-xr-x. 1 root root 525856 May 9 15:09 sqlcmd
[oracle@hmc-P55A-UD3 bin]$ pwd
/opt/mssql-tools/bin
[oracle@hmc-P55A-UD3 ~]$ sqlcmd -U perfstat -S 192.168.0.181,1433
Password:
1> select count(*) from sys.sql_logins;
2> go;
3> go
Msg 102, Level 15, State 1, Server MP04ISLLIQY1\SQLEXPRESS, Line 2
Incorrect syntax near 'go'.
1> select count(*) from sys.sql_logins;
2> go
-----------
4
(1 rows affected)
Tuesday, August 08, 2017
SQL server audit related links
Collected some related to SQL server audit setup, writing to windows event log.
Auditing Failed Logins in SQL Server
How-To Write SQL Server Audit Events To Windows Security Log
After following these steps, you should successfully start seeing your Microsoft SQL Server Audit Logs in the Windows Security Log. They’re usually listed as Event Code 33205, so you can filter your Security Logs to only show those events.
SQL Server Audit (Database Engine)
All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2016 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions
Any authenticated user can read and write to the Windows Application event log. The Application event log requires lower permissions than the Windows Security event log and is less secure than the Windows Security event log.
Attaching a database that has an audit specification and specifies a GUID that does not exist on the server will cause an orphaned audit specification. Because an audit with a matching GUID does not exist on the server instance, no audit events will be recorded. To correct this situation, use the ALTER DATABASE AUDIT SPECIFICATION command to connect the orphaned audit specification to an existing server audit. Or, use the CREATE SERVER AUDIT command to create a new server audit with the specified GUID.
You can attach a database that has an audit specification defined on it to another edition of SQL Server that does not support SQL Server audit, such as SQL Server Express but it will not record audit events.
Members of the sysadmin fixed server role are identified as the dbo user in each database. To audit actions of the administrators, audit the actions of the dbo user.
Creating and Managing Audits with SQL Server Management Studio
Audit-related nodes are located as follows in the Object Explorer hierarchy in Management Studio.
SQL Server Instance
---- Security
-------- Audits
-------- Server Audit Specifications
SQL Server Instance
---- Databases
-------- Database Name
------------ Security
---------------- Database Audit Specifications
Create a Server Audit and Server Audit Specification
Create a Server Audit and Database Audit Specification
SQL Server Audit Action Groups and Actions
Write SQL Server Audit Events to the Security Log
SQL Server Audit Support in Different Editions and Versions ( info for version prior to sql 2016)
Sat, 23 Nov 2013 14:02:05 GMT
Here is a summary of the SQL Server Audit support in the different editions:
sql server ports 445 and 1433
445 is not a SQL port, is a SMB port. It is involved in SQL Server only if you use named pipes protocol, as named pipes are over SMB and this in turn uses 445 for 'SMB over IP', aka. as SMB 'NETBIOSless' as opposed to the old NetBIOS based SMB, which uses 137-139.
To configure the server to listen on specific protocols, use SQL Server configuration Manager. To configure the client allowed protocols, see Configuring Client Network Protocols.
Auditing Failed Logins in SQL Server
How-To Write SQL Server Audit Events To Windows Security Log
After following these steps, you should successfully start seeing your Microsoft SQL Server Audit Logs in the Windows Security Log. They’re usually listed as Event Code 33205, so you can filter your Security Logs to only show those events.
SQL Server Audit (Database Engine)
All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2016 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions
Any authenticated user can read and write to the Windows Application event log. The Application event log requires lower permissions than the Windows Security event log and is less secure than the Windows Security event log.
Attaching a Database with an Audit Defined
Attaching a database that has an audit specification and specifies a GUID that does not exist on the server will cause an orphaned audit specification. Because an audit with a matching GUID does not exist on the server instance, no audit events will be recorded. To correct this situation, use the ALTER DATABASE AUDIT SPECIFICATION command to connect the orphaned audit specification to an existing server audit. Or, use the CREATE SERVER AUDIT command to create a new server audit with the specified GUID.
You can attach a database that has an audit specification defined on it to another edition of SQL Server that does not support SQL Server audit, such as SQL Server Express but it will not record audit events.
Auditing Administrators
Members of the sysadmin fixed server role are identified as the dbo user in each database. To audit actions of the administrators, audit the actions of the dbo user.
Creating and Managing Audits with SQL Server Management Studio
Audit-related nodes are located as follows in the Object Explorer hierarchy in Management Studio.
SQL Server Instance
---- Security
-------- Audits
-------- Server Audit Specifications
SQL Server Instance
---- Databases
-------- Database Name
------------ Security
---------------- Database Audit Specifications
Create a Server Audit and Server Audit Specification
Create a Server Audit and Database Audit Specification
SQL Server Audit Action Groups and Actions
Write SQL Server Audit Events to the Security Log
SQL Server Audit Support in Different Editions and Versions ( info for version prior to sql 2016)
Sat, 23 Nov 2013 14:02:05 GMT
Here is a summary of the SQL Server Audit support in the different editions:
| click me | click me | click me |
|---|---|---|
| Edition | SQL Server 2008 and 2008 R2 | SQL Server 2012 and 2014 |
| Enterprise | Server- and database-level | Server- and database-level |
| Evaluation | Server- and database-level | Server- and database-level |
| Developer | Server- and database-level | Server- and database-level |
| Datacenter | Server- and database-level | N/A |
| Business Intelligence | None | Server-level |
| Standard | None | Server-level |
| Web | None | Server-level |
| Express | None | Server-level |
sql server ports 445 and 1433
445 is not a SQL port, is a SMB port. It is involved in SQL Server only if you use named pipes protocol, as named pipes are over SMB and this in turn uses 445 for 'SMB over IP', aka. as SMB 'NETBIOSless' as opposed to the old NetBIOS based SMB, which uses 137-139.
To configure the server to listen on specific protocols, use SQL Server configuration Manager. To configure the client allowed protocols, see Configuring Client Network Protocols.
Tuesday, July 04, 2017
Friday, June 30, 2017
Use full links related to SQL server baselines
Marked some useful links during my review of SQL server security baselines, so as to close my precious resource of Chrome browser tabs. As for each google, I commonly open 3 links ...
Review the old copy of this baseline had been my dream, to make it easier for DBA.
Server Configuration Option related
audit
remote admin connections Server Configuration Option
Configure the remote access Server Configuration Option (2008)
xp_cmdshell Server Configuration Option
How to check trustworthy property of the databases
scan for startup procs Option
Permission
Security announcement
User logins
select * from sys.sql_logins;
Server configuration
Extended Stored Procedure
Friday, September 30, 2016
SQL Server 2000 audit
There might be a requirement to audit failure logins.
sp_readerrorlog works in charm for version 2005 and above, however it is not in sql server 2000.
As it has different format for parameters. After spent some time, I finally find out how to use it correctly.
eg.
Exec sp_readerrorlog 1,'D:\test.txt' , 'world' # grep all lines having the word
Exec sp_readerrorlog 1,'D:\test.txt' , 3 # read 3rd line
Exec sp_readerrorlog 1,'D:\mediator_dev\MSSQL$MED\LOG\ERRORLOG','Backup'
from
http://www.databasejournal.com/features/mssql/article.php/3399241/SQL-Server-2000-Security---Part-10---Auditing.htm
Login auditing - four option buttons - None, Success, Failure and All - under the "Audit level" heading on the Security tab of the SQL Server Properties dialog box in SQL Server Enterprise Manager control level of SQL Server login audits. These choices correspond to values of 0, 1, 2 and 3 in the AuditLevel entry in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\ registry key AuditLevel of DWORD data type. This value determines whether login attempts with specified outcome (or any at all) will be recorded in the Windows Application Event Log and SQL Server error log.
It seems by default audit level is 0.
References:
http://vyaskn.tripod.com/sp_readerrorlog_undocumented.htm
sp_readerrorlog works in charm for version 2005 and above, however it is not in sql server 2000.
As it has different format for parameters. After spent some time, I finally find out how to use it correctly.
eg.
Exec sp_readerrorlog 1,'D:\test.txt' , 'world' # grep all lines having the word
Exec sp_readerrorlog 1,'D:\test.txt' , 3 # read 3rd line
Exec sp_readerrorlog 1,'D:\mediator_dev\MSSQL$MED\LOG\ERRORLOG','Backup'
from
http://www.databasejournal.com/features/mssql/article.php/3399241/SQL-Server-2000-Security---Part-10---Auditing.htm
Login auditing - four option buttons - None, Success, Failure and All - under the "Audit level" heading on the Security tab of the SQL Server Properties dialog box in SQL Server Enterprise Manager control level of SQL Server login audits. These choices correspond to values of 0, 1, 2 and 3 in the AuditLevel entry in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\
It seems by default audit level is 0.
References:
http://vyaskn.tripod.com/sp_readerrorlog_undocumented.htm
Monday, November 23, 2015
SQL server 2000 profiler
One system is getting slower and slower, so I decided to use profiler for investigation.
Googled and read out the following useful articles:
http://serverfault.com/questions/162245/how-do-i-identify-slow-queries-in-sql-server
http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step
to minimize changes to production, I select using tracing file instead of a table.
confirmed with below two articles for finding out indexes columns.
https://bytes.com/topic/sql-server/answers/562317-how-do-i-find-indexes-columns-sql-server-2000-a
http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db
Finally, I got a huge return -- the system has ZERO indexes created.
Googled and read out the following useful articles:
http://serverfault.com/questions/162245/how-do-i-identify-slow-queries-in-sql-server
http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step
to minimize changes to production, I select using tracing file instead of a table.
confirmed with below two articles for finding out indexes columns.
https://bytes.com/topic/sql-server/answers/562317-how-do-i-find-indexes-columns-sql-server-2000-a
http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db
Finally, I got a huge return -- the system has ZERO indexes created.
Monday, July 20, 2009
How do I connect to a non-default instance of SQL Server?
The convention of "Data Source" (for creating link server) or "Server Name" (called in management studio" is : ,/ or /,
Here is a connection string that attaches to an instance of SQL Server that is NOT the default instance.
cst = "Provider=SQLOLEDB;" & _
"Data Source=127.0.0.1\instanceName;" & _
"Initial Catalog=pubs;" & _
"Network=DBMSSOCN;" & _
"User ID=;" & _
"Password="
set conn = CreateObject("ADODB.Connection")
conn.open cst
If your instance is running on a non-default port (by default, SQL Server runs on 1433):
cst = "Provider=SQLOLEDB;" & _
"Data Source=127.0.0.1,1510\instanceName;" & _
"Initial Catalog=pubs;" & _
"Network=DBMSSOCN;" & _
"User ID=;" & _
"Password="
set conn = CreateObject("ADODB.Connection")
conn.open cst
Here is a connection string that attaches to an instance of SQL Server that is NOT the default instance.
cst = "Provider=SQLOLEDB;" & _
"Data Source=127.0.0.1\instanceName;" & _
"Initial Catalog=pubs;" & _
"Network=DBMSSOCN;" & _
"User ID=
"Password=
set conn = CreateObject("ADODB.Connection")
conn.open cst
If your instance is running on a non-default port (by default, SQL Server runs on 1433):
cst = "Provider=SQLOLEDB;" & _
"Data Source=127.0.0.1,1510\instanceName;" & _
"Initial Catalog=pubs;" & _
"Network=DBMSSOCN;" & _
"User ID=
"Password=
set conn = CreateObject("ADODB.Connection")
conn.open cst
Thursday, January 15, 2009
truncate sql server transactional log withou backcup
sometimes on fire and the risk to truncate sql server without backup up.
backup log with TURNCATE_ONLY;
it works on sql server 2000.
but looks no more supported on 2008 . no time to check 2005 ..
backup log
it works on sql server 2000.
but looks no more supported on 2008 . no time to check 2005 ..
Wednesday, September 10, 2008
timing of shrink database vs shrink datafile
I have a sql server 2005 database with single data file and transaction log. The data file size is 40Gb and there is 23Gb free space.
So I want to shrink it by click "shrink database". By the help of belw sql, it only completed 50% even after running 3.5 hours.
select percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, * from sys.dm_exec_requests;
I decide to stop it and change to use "shrink file". Surprising, it completed less than 1 minutes and return 7Gb to disk space.
Funny.
So I want to shrink it by click "shrink database". By the help of belw sql, it only completed 50% even after running 3.5 hours.
select percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, * from sys.dm_exec_requests;
I decide to stop it and change to use "shrink file". Surprising, it completed less than 1 minutes and return 7Gb to disk space.
Funny.
Friday, April 18, 2008
lost domain controller leads to SQL server 26-Error
[THE PROBLEM
]TITLE: Connect to Server
------------------------------
Cannot connect to m1dwdev\dw_dev.
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
Many are discussed in http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx
but none is related to domain controller.
The error message in event viewer is:
Event Type: Error
Event Source: NETLOGON
Event Category: None
Event ID: 5719
Date: 4/18/2008
Time: 4:47:37 AM
User: N/A
Computer: M1DWDEV
Description:
No Windows NT or Windows 2000 Domain Controller is available for domain M1. The following error occurred:
There are currently no logon servers available to service the logon request.
Data:
0000: 5e 00 00 c0 ^..à
[THE SOLUTION]
Disable/Enable network. Problem solved.
]TITLE: Connect to Server
------------------------------
Cannot connect to m1dwdev\dw_dev.
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
Many are discussed in http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx
but none is related to domain controller.
The error message in event viewer is:
Event Type: Error
Event Source: NETLOGON
Event Category: None
Event ID: 5719
Date: 4/18/2008
Time: 4:47:37 AM
User: N/A
Computer: M1DWDEV
Description:
No Windows NT or Windows 2000 Domain Controller is available for domain M1. The following error occurred:
There are currently no logon servers available to service the logon request.
Data:
0000: 5e 00 00 c0 ^..à
[THE SOLUTION]
Disable/Enable network. Problem solved.
Thursday, April 03, 2008
BUG: Error 7356 from a Distributed Query
APPLIES TO
• Microsoft SQL Server 7.0 Standard Edition
???
In my env, we still hit the problem with SQL server 2000 SP4.
• Microsoft SQL Server 7.0 Standard Edition
???
In my env, we still hit the problem with SQL server 2000 SP4.
Tuesday, August 07, 2007
Determining Space used by Filegroups
http://forums.databasejournal.com/archive/index.php/t-30789.html
dbcc molestations( n )
n is the fileid which you can get from
select fileid,name from sysfiles
Here is a query that simulates sp_spaceused, modified to group by groupid (you can look up the filegroup names in sysfilegroups):
SELECT
groupid,
SUM(CASE WHEN indid <> 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY groupid
ORDER BY groupid;
http://www.databasejournal.com/features/mssql/article.php/10894_3414111_2
dbcc molestations( n )
n is the fileid which you can get from
select fileid,name from sysfiles
Here is a query that simulates sp_spaceused, modified to group by groupid (you can look up the filegroup names in sysfilegroups):
SELECT
groupid,
SUM(CASE WHEN indid <> 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY groupid
ORDER BY groupid;
http://www.databasejournal.com/features/mssql/article.php/10894_3414111_2
Subscribe to:
Posts (Atom)
