bulk insert working in ssms but not working while executing from php

Question

I'm trying to insert the data from a text file into my database. I'm using SQL Server Management Studio 17.

I'm using this query:

BULK INSERT db.[dbo].[mytable]
    FROM 'path/file.txt'
    WITH 
        (FIELDTERMINATOR = ';',
         ROWTERMINATOR = '\n')

The problem is that it's working in SQL Server Management Studio but when i'm trying to execute this php script it shows an error even though I granted the user the permission to bulk after following this:

You do not have permission to use the bulk load statement

The error:

Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 4834 [code] => 4834 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]You do not have permission to use the bulk load statement. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]You do not have permission to use the bulk load statement. ) )

The code i'm using

<?php

$serverName = "SQLEXPRESS"; 
$connectionInfo = array( "Database"=>"master");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}    
$query1 = "BULK INSERT db.[dbo].[mytable]
    FROM 'path/file.txt'
    WITH 
        (FIELDTERMINATOR = ';',
         ROWTERMINATOR = '\n')";

$result1 = sqlsrv_query($conn,$query1) or  die( print_r(sqlsrv_errors(), true) );

?>

I would suggest that the user does not have the right permissions. Try ALTER SERVER ROLE bulkadmin ADD MEMBER {Your PHP SQL Login}; (obviously replace the text in the braces ({})). — Thom A
– Thom A ♦, Commented Feb 11, 2019 at 16:55

Alexander Volok · Accepted Answer · 2019-02-11 17:10:32Z

1

Account that is used by your PHP application need this permission:

GRANT ADMINISTER BULK OPERATIONS TO [server_login]

Your SSMS process seems run using other credentials and they have such permission.

The other reason can be if SQL Server service account does not have enough permissions to read the source file

edited Feb 11, 2019 at 17:10

answered Feb 11, 2019 at 16:56

Alexander Volok

5,9903 gold badges19 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Morsi.Hamza Over a year ago

Yes, it turns out that i'm using other credentials. The weird thing is even i'm using in PHP and SSMS windows authentication, the user in PHP script is different from SSMS. I figured that out in error.log. I think that's another issue. Thank you for your help.

Alexander Volok Over a year ago

@Morsi.Hamza, is your question answered? If so, consider to mark it as such one.

Collectives™ on Stack Overflow

bulk insert working in ssms but not working while executing from php

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related