I hope i am not asking silly question but i have scenario when insert from mssql management studio to remote database (i have no control of) works but the insert from the ignition sql browser generates an error (it does work when writing to the local mssql table). I am only able to select the data from the remote database.
My understanding is that it must be permission/role related but struggling to get any other diagnostic.
Do you know if ignition needs more permissions than mssql studo to insert data to remote db? Would you recommend any tools to diagnose the issue further ? I will paste the wrapper file entry but it was suggesting that the format of the insert was not right but it works when using mssql studio and also works when inserting from ignition to the local table.
Really puzzled.
Hello, I don't use MS SQL often but there is DB reader and DB writer.
It sounds like you need to talk to the DBA and get write permissions assigned to the user that is configured in the Ignition gateway for this remote DB.
p.s; are you logged into SSMS as the same role as Ignition is using?
Any time I've needed to connect to a remote MSSQL database I have set up named pipes. Usually I create an SQL user on the database and in most of my applications I give the user read-only permissions. I also set the authentication to SQL rather than Windows credentials so that it's a simple username/password connection to get connected without any Windows account problems.
See if this article helps:
yes the same roles on both
thank you for this link.
Are named pipes alternative to TCP/IP with less overhead? My understanding is that named pipes can be used only if ignition and mssql server are on the same machine - is it right ?
I don't know enough to answer that.
No, I use them specifically to access databases on other machines. Typically I was using them so that my PHP webserver could read data from HMI SQL databases.
IP won the network protocol wars. TCP/IP is interoperable across operating systems and is routable to machines across the world. It is a living standard and security is being improved constantly. The TCP/IP driver gets to use these improvements, sometime without even knowing about the improvements.
Named Pipes are not generally routable across an IP network. They may be specific to the OS of the endpoint machines and network servers. In some environments, Named Pipes may be limited to a single machine. In other environments a network segment may be able to use Named Pipes.
thanks for your comment.
@Tim_Carlton how would you highlight the benefits of using named pipes?
No, I use them specifically to access databases on other machines. Typically I was using them so that my PHP webserver could read data from HMI SQL databases.
on the same network - right?
I gave up long ago and use TCP/IP. It is probably faster - much faster. So if I am on a trusted homogeneous network I might try it once but if it doesn't work I move on pretty quickly.
All in the same building but on different subnets so there were multiple routers between them.