Write data back from Excel

DenSter

This is not about exporting to and importing from Excel, but about direct data connections from Excel into the SQL Server database. I searched for all sorts of Excel strings, and got like 6000 export/import topics, but cannot find any information about direct connections.

You can get data in Excel by opening a direct data connection into SQL Server. Can this be a 2-way data connection, and can people write data back to SQL Server this way? Is it possible to ensure that these connections are read-only, and how do we make sure that they are? Does it matter which security model the NAV database is in?

ara3n

I suggest to use webservices.

Here is Freddy blog on this. If the client is not on 2009, they can do an exe upgrade and use webservices

http://blogs.msdn.com/freddyk/archive/2 ... -of-2.aspx

http://blogs.msdn.com/freddyk/archive/2 ... -of-4.aspx

http://blogs.msdn.com/freddyk/archive/2 ... -of-4.aspx

KTA8

You can do that. I think Jet Reports works against the SQL database directly.

matttrax

I have never actually done this, but have played around with it some. Maybe I can get the ball rolling in the right direction.

In Excel you do have the ability to directly connect to a SQL database. I don't believe it matters what security model you have. I'm a SUPER user on everything so I've never checked to see if you have to resynch the NAV permissions in order to read the table from Excel.

To ensure that it is read only you have to "unlink" the Excel page from the database. This keeps the data in Excel, but the page will no longer update with new data. So the query is removed from Excel and there is no longer any sort of connection.

As for writebacks, I'm pretty sure you'd have to write a macro for that, but it could be done.

Hope that's at least a little bit of help.

DenSter

Thanks guys. The goal mainly for me is to learn two things:

• How 2-way data connections between SQL Server and Excel work, WITHOUT any 3rd party tools or webservices
• How to PREVENT write back from Excel, i.e. how to ensure that this connection is read-only, in general, but for NAV specifically

ara3n

DenSter wrote:

Thanks guys. The goal mainly for me is to learn two things:

• How 2-way data connections between SQL Server and Excel work, WITHOUT any 3rd party tools or webservices
• How to PREVENT write back from Excel, i.e. how to ensure that this connection is read-only, in general, but for NAV specifically

You would have to write and instead of using webservice use ADO just like freddy is doing it.

The credentials you are providing in your code would need to have SQL datareader role only.

DenSter

ara3n wrote:

The credentials you are providing in your code would need to have SQL datareader role only.

So that is a scenario where you'd select credentials that have datareader only, which does not address the scenario where someone gets data into Excel with credentials that have write permissions. I need to know if it's possible to block all write access to the NAV database from Excel (or any other applications).

Let's say you are NAV SUPER user, and you want to use Excel to modify sales orders. How do I set up the system so that you can't do that, while at the same time keeping your SUPER creds intact.

ara3n

DenSter wrote:

ara3n wrote:

The credentials you are providing in your code would need to have SQL datareader role only.

So that is a scenario where you'd select credentials that have datareader only, which does not address the scenario where someone gets data into Excel with credentials that have write permissions. I need to know if it's possible to block all write access to the NAV database from Excel (or any other applications).

Let's say you are NAV SUPER user, and you want to use Excel to modify sales orders. How do I set up the system so that you can't do that, while at the same time keeping your SUPER creds intact.

When Nav user connects to SQL, Navision uses the application Role. The application role has the SUPER role, not the user. So with the SUPER Application role they do all the things they can do.

When the user connects directly with EXCEL, they use their user permission. And on sql they have just the public role. With this role they cannot read or modify any tables.
By providing that user the datareader role they will be able to read data.

DenSter

ara3n wrote:

When Nav user connects to SQL, Navision uses the application Role. The application role has the SUPER role, not the user. So with the SUPER Application role they do all the things they can do.

When the user connects directly with EXCEL, they use their user permission. And on sql they have just the public role. With this role they cannot read or modify any tables.
By providing that user the datareader role they will be able to read data.

So you're saying that for regular users, that only have public, there's no chance they can read or write NAV data in Excel? Does it make a difference whether the database is on enhanced or standard security model?

Another question: can we block users that are sysadmin from writing data from Excel?

garak

yes on the sql server self. You can find out there, wich user connect and from wich application they connect. I posted here (but i can't find it), as i know, a sql codesnippes, to find out on the sql server: which user connect, from which client and what for an application he use to connect. The base was to use the: sys.dm_exec_sessions (>= Sql2005) and a job that checks the connections (> SPID 50)

regards

ara3n

DenSter wrote:

So you're saying that for regular users, that only have public, there's no chance they can read or write NAV data in Excel? Does it make a difference whether the database is on enhanced or standard security model?

yes

Another question: can we block users that are sysadmin from writing data from Excel?

you could write a sql trigger to prevent the user from inserting. You can check the application type that is inserting the record.
You can look at the session view sql statement to get the application example name and error out on the sql trigger.