The powershell cmdlet Write-SqlTableData is erroring for me, if I run it as a user with db_datawriter permissions; but I'd have thought that would be enough. If I use the sa account, it works.
Is there some deliberate reason why an ordinary user with db_datareader and db_datawriter cannot be used for this command?
In my powershell script, I'm printing the credentials object and parameter object before calling Write-SqlTableData with splatting, to help debug. Here is the output in the version where it fails:
UserName Password -------- -------- apitest1_EngageAI1_import_login01 System.Security.SecureString Name Value ---- ----- Credential System.Management.Automation.PSCredential SchemaName dbo TableName AppInsights ServerInstance REDACTEDserver1.database.windows.net,1433 InputData {@{timestamp=04/01/2024 23:47:12; customer=apitest1; user_Id=a9fa9bf9-33c6-4e3d-ac27-49… DatabaseName EngageAI1 ##[error]Failed to connect to server REDACTEDserver.database.windows.net,1433. ##[error]PowerShell exited with code '1'.
I also faced the similar issue. the issue is because the use is not present in master database of azure sql.
To resolve this issue, follow below queries to create users:
-- master databse
CREATE LOGIN myadmin WITH PASSWORD = '<some password>'
CREATE USER myadmin FOR LOGIN myadmin
For each database on the server
CREATE USER myadmin FROM LOGIN myadmin
EXEC sp_addrolemember 'db_datawriter', 'myadmin'
EXEC sp_addrolemember 'db_datareader', 'myadmin'
Use below PowerShell script to write data in table:
Import-Module SqlServer
$cred = Get-Credential -Message "Enter your SQL Auth credentials"
$cred.Password.MakeReadOnly()
$srv = Get-SqlInstance -ServerInstance "server-name.database.windows.net" -Credential $cred
$db = $srv.Databases["database-name"]
$table = $db.Tables["table-name"]
Write-SqlTableData -InputData (1,4) -InputObject $table
Read-SqlTableData -InputObject $table