How to setup SqlServer to access via Internet – sql authentication

How to setup SqlServer to access via Internet – sql authentication

There three main parts in setting up sql server to be accessed on the internet.

  1. Configure your SqlServer
    • Setup Sql ports
      • run Sql configuration manager
      • goto sql server network configuration
      • enabled tcp
      • goto sql server service
      • restart the sql service instance
      • goto back to sql server network configuration
      • click on tcp properties
      • under port put 1433 or anyother ports
    • Setup Database access
      • run sql managment studio
        • connect to database locally
        • goto security, login
        • right click choose new login
        • under general –> goto left panel put username, under sql authentication put passoword
        • under server roles –> choose sysadmin
      • right click on the main server
        • goto security make sure the Sql and windows authentication mode is ticked
        • goto connections –> make sure allow to remote connection is ticked
      • after you restore a database, you need to give access
        • sometimes get a error  prinicpal dbo does not exist on server
        • ALTER AUTHORIZATIONON ATABASE::[YourDatabaseName]TO[LoginUser];  –> loginuser is the username you just created
  2. Configure your Firewall
    • On windows machine –> goto firewall –>
    • advanced –> setup inbound rules to allow tcp 1433
    • if u have external firewall –> also make sure tcp 1433 is allowed
  3. Test ..
    • u initally test from pc ..
      • run netstat -n -a
      • u should see 0.0.0.0:1433
      • if do telnet <ip> 1433 this should be allow
    • finally goto to sql management studio
      • connect to database
      • note the external ip address and port, as sql instance
      • 53.232.3.23,1433\sqlexpress
      • put the sql username, password

Leave a Reply

Your email address will not be published. Required fields are marked *