Open port for SQL Server remote connection
Ports and protocols used by Microsoft SQL Server database engine (SQLServer).
About this task
Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft. Its primary query language is Transact-SQL, an implementation of the ANSI/ISO standard Structured Query Language (SQL) which is used by Microsoft. You need to allow distant users to connect to the SQL server so they can address it their queries.
SQL Server is used by:
Restoring database from .mdf and .ldf files of SQL Server 2008
- First Put the
.mdf
and.ldf
file inC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA
folder - Then go to sql software , Right-click “Databases” and click the “Attach” option to open the Attach Databases dialog box
- Click the “Add” button to open and Locate Database Files From
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA
folder - Click the “OK” button. SQL Server Management Studio loads the database from the
.MDF
file.
Solving SQL Server Connection Problem
Inspect Server Settings
There are three significant aspects that users may neglect, which lead to the failure of the connection to the SQL Server.
Enable TCP/IP Connection
To check whether your server enabled to TCP/IP connections:
Uninstall Oracle DB on Windows completely
Manually
- START -> RUN -> Services.msc
- Locate all Ora* services & click on STOP
- Start -> RUN -> Regedit
- Locate HKEY_LOCAL_MACHINE folder in the registry
- Open Software folder
- Delete the Oracle folder under Software
- Open System folder in HKEY_LOCAL_MACHINE
- Open CurrentControlSet folder within the System folder
- Open Services
- Delete all keys related to Oracle. Every key starts with “ORA..”
- Close the regedit
- Delete ORACLE_HOME folder
- Delete the Oracle folder in Program Files
- Reboot
Uninstall all Oracle components using the Oracle Universal Installer (OUI).
Grant privilege on all objects in a Schema to a user in Oracle Database
In this example I have given select on all tables in schema test1 to user test2. As well grant all DML privilege on tables and views, and grant execute on procedures, functions and packages in a schema test1 to user test2.
SQL> create user test1 identified by test1; User created. SQL> grant connect,resource to test1; Grant succeeded. SQL> create user test2 identified by test2; User created. SQL> grant connect,resource to test2; Grant succeeded. SQL> conn test1/test1 Connected. SQL> create table test_table1(id number,name varchar2(30)); Table created. SQL> create table test_table2(id number,name varchar2(30)); Table created. SQL> show user; USER is "TEST1"
If you want to grant select privilege:
Oracle database Commands
SSH vào server bằng tài khoản oracle.
Truy cập công cụ sqlplus, đăng nhập quyền sysdba:
sqlplus / as sysdba
Thông thường, có thể lệnh sqlplus có thể sẽ không tồn tại trong session hiện tại, bạn cần thực hiện câu lệnh sau để áp dụng biến môi trường (xem trong bài Install Oracle 11g R2 on CentOS):
Install Oracle 12c R2 on CentOS
Cài đặt GUI
Đầu tiên cần cài đặt giao diện GUI cho CentOS, làm theo bài hướng dẫn sau: Install Gnome 3 GUI on RHEL, CentOS
Download bộ cài
Download bộ cài Oracle 12c từ trang chủ của Oracle hoặc bằng link Google Drive tại đây:
Install Oracle 11g R2 on CentOS
Cài đặt GUI
Đầu tiên cần cài đặt giao diện GUI cho CentOS, làm theo bài hướng dẫn sau: Install Gnome 3 GUI on RHEL, CentOS
Download bộ cài
Download bộ cài Oracle 11g từ trang chủ của Oracle hoặc bằng link Google Drive tại đây: