CODEWITHSUNDEEP
sql-serveramazon-web-services
NitinSingh
NitinSingh

Reputation: 2068

AWS Schema Conversion Tool to Connect to SQL Server on localhost

Background I have a SQL Server database on my machine, using mixed mode auth and SQL users setup on the database and mapped. When connecting via .NET app, I am able to use both windows auth and sql auth without issues. However when connecting via AWS Schema Migration tool, I am not able to connect.

Checks done:-

  1. SQL Server Browser Service running
  2. Port mapping left to default (1433)
  3. TCP/IP pipeline enabled
  4. Done both sql and windows auth
  5. Instance name given via @@servername command as per SCT doc
  6. Windows user given dbo and serveradmin permissions at global level and also granted Login and DB engine connect
  7. Ignoring the port no
  8. Ignoring the instance name
  9. Unable to check firewall as both SQL and tool running on same machine

Using same SCT config, I can connect to SQL Server elsewhere so drivers seem to be working.

Log message below

  1. When only localhost provided

    com.amazon.sct.dbloader.DbLoaderHandledException: Connection wasn't established. Check connection properties. java.util.concurrent.ExecutionException: com.amazon.sct.dbloader.DbLoaderHandledException: Connection wasn't established. Check connection properties.

  2. When name provided for machine and instance

    Connection to 'jdbc:sqlserver://localhost\DESKTOP-N01JO12:1433' wasn't established. ERROR: code: 0; message: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

    LOADER ERROR Error chain: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall."

Screenshots

AWS SCT Connect screenshot with SQL Auth AWS SCT Connect screenshot with Win Auth

Upvotes: 1

Views: 1840

Answers (1)

Anh Minh
Anh Minh

Reputation: 1

For AWS SCT can access SQL server, the following conditions need to be satisfied:

  • DB has login account and user
  • User have to has admin role
  • SQL server authentication enabled
  • TCP/IP cho SQL server enabled

1. DB has login account and user

You can reference in this link: https://www.guru99.com/sql-server-create-user.html

2. User have to has admin role

You can reference in this link: https://tableplus.com/blog/2018/10/how-to-create-user-with-admin-access-in-sql-server.html

3. SQL server authentication enabled

You can reference in this link: SqlServer: Login failed for user

Note: Remember to restart SQL server

4. TCP/IP cho SQL server enabled

You can reference in this link: https://www.youtube.com/watch?v=p9mv2RP6Tck

==> Conclusion

I see you do all the think above

Here is my connection information for SQL server on my EC2 windows server

(I can't embed picture because I don't have enough reputation point)

Perhaps you should write connection information like me

Hope this help!

Upvotes: 0

Related Questions