question

laggyluk avatar image
laggyluk asked

Querying Azure SQL database from playfab VM

In my existing setup: player inventory is held in sql database on azure. ue4 dedicated server is communicating with it.

Or at least, it does in my local setup.


Can someone advise me how to configure the playfab VM for that?

I assume VM is booted from disk image I can't alter permanently but maybe can run some .bat that sets things up and then launches server .exe?

In the dedicated server logs on VM when trying to query the database I'm getting following error:

IM002:1:0:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

so I guess I need to install the odbc driver first and then probably set the firewall for communicating with azure?

unrealdata
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
laggyluk avatar image
laggyluk answered

Welp, turns out I don't need to install odbc drivers since there's one called 'SQL Server' already installed. Changed my connection string to use it instead of what I was trying to install and now query works.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Citrus Yan avatar image Citrus Yan commented ·

Glad to know that you figured it out! And, after a discussion with our engineer team, we must inform you that it's not a good security practice to have the game server connect directly to SQL server as that would allow an attacker to compromise the entire SQL Server if your game servers has security flaws. It's suggested to create an API service that connects to SQL Server and use some form of authentication mechanisms to authenticate the caller, for instance, Azure Functions could be a reliable solution.

0 Likes 0 ·
laggyluk avatar image laggyluk Citrus Yan commented ·

ok, thanks for the info. I'm a one person dev team so there are gonna be a lot of short comings in this title ;p

Can't this risk be minimized by having separate sql user for game server connection with rights to only execute stored procedures?

0 Likes 0 ·
Citrus Yan avatar image
Citrus Yan answered

First of all, your servers are operated as containerized applications by PlayFab, not directly run on the PlayFab VM, however the basic ideas here are similar though. Currently, ODBC drivers are not installed in our Windows docker image, you can write a .bat file that installs the odbc driver & launchs the server, then in the Start command, runs the .bat file.

7 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

laggyluk avatar image laggyluk commented ·

tried with .bat file but looks like install fails due to lack of admin privilege

bat command:

msiexec.exe /i msodbcsql.msi /QN /L*V "setup_log.txt" IACCEPTMSODBCSQLLICENSETERMS=YES

log:

MSI (s) (CC:F8) [20:25:15:997]: Package to be registered: 'msodbcsql.msi'
MSI (s) (CC:F8) [20:25:15:998]: Note: 1: 2262 2: AdminProperties 3: -2147287038 
MSI (s) (CC:F8) [20:25:15:998]: Machine policy value 'AlwaysInstallElevated' is 0
MSI (s) (CC:F8) [20:25:15:998]: User policy value 'AlwaysInstallElevated' is 0
MSI (s) (CC:F8) [20:25:15:998]: Rejecting product '{DBCD7798-30AE-474B-A733-717996DCCA41}': Non-assigned apps are disabled for non-admin users.
MSI (s) (CC:F8) [20:25:15:999]: Note: 1: 1708 
MSI (s) (CC:F8) [20:25:15:999]: Product: Microsoft ODBC Driver 17 for SQL Server -- Installation failed.
0 Likes 0 ·
JayZuo avatar image JayZuo ♦ laggyluk commented ·

I cannot reproduce your issue. I tested with your command with one modification:

msiexec.exe /i msodbcsql.msi /QN /L*V "c:\GameLogs\setup_log.txt" IACCEPTMSODBCSQLLICENSETERMS=YES

and it worked well:

MSI (s) (58:80) [11:17:12:417]: Product: Microsoft ODBC Driver 17 for SQL Server -- Installation completed successfully.


MSI (s) (58:80) [11:17:12:417]: Windows Installer installed the product. Product Name: Microsoft ODBC Driver 17 for SQL Server. Product Version: 17.6.1.1. Product Language: 1033. Manufacturer: Microsoft Corporation. Installation success or error status: 0.


MSI (s) (58:80) [11:17:12:448]: Deferring clean up of packages/files, if any exist
MSI (s) (58:80) [11:17:12:448]: MainEngineThread is returning 0
MSI (s) (58:70) [11:17:12:464]: RESTART MANAGER: Session closed.
MSI (s) (58:70) [11:17:12:464]: No System Restore sequence number for this installation.
=== Logging stopped: 11/27/2020  11:17:12 ===

I'd suggest testing with Docker locally as https://docs.microsoft.com/en-us/gaming/playfab/features/multiplayer/servers/locally-debugging-game-servers-and-integration-with-playfab#verifying-containerization.

0 Likes 0 ·
laggyluk avatar image laggyluk JayZuo ♦ commented ·

Ok I'll try some more but was the driver installed though? I've managed to get it 'installed' but it only appeared as success in log, in the ODBC Data Sources panel the new driver didn't appear unless installed from privileged cmd.

0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.