MacOs User connection to SQL Server from Excel

  • We have a financial reporting tool , which gets data from SQL server via stored procedure and works fine on Windows computers. (Please forgive me if I am using the wrong terms in the next few lines, I am neither IT nor native speaker):

    If they work on a Windows netbook, they logon on their computer to our company domain when they start up, such identifying themselves (windows authentification?). We have different user groups defined, which we also use to access network shares, where specific users are entered, and this users are given specific permissions on the serves via a database role. I define the connection string in VBA

    • Provider=SQLOLEDB;Data Source=SERVERNAME;Initial Catalog=ASAP_be;

      or second connection string:

    • "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ASAP_be;Data Source=SERVERNAME;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=M99999;Use Encryption for Data=False;Tag with column collation when possible=False" Trusted_Connection=yes")

    and this works fine for windows users.

    But for mac users, they have (had?) to logon to a citrix client and then start the Workbook, which runs on a windows users. Now with office 2019, at least VBA seems to be (almost) the same, so it would be nice, if they could start the procedure directly from Excel on mac. Somehow Excel does not give us the solution to use the user login and password to connect to the SQL-Server.

    Can you tell me what we can do on SQL-Server or in VBA - Excel, so that Mac Users can refresh their data from SQL-Server without having to define permissions additionally?

    • This topic was modified 4 years, 6 months ago by  christoph.kauflin. Reason: added topic tags
    • This topic was modified 4 years, 6 months ago by  christoph.kauflin. Reason: clarification /typo

    Christoph Kauflin
    working in Controlling, but trying to program the tools we need to do that

  • Thanks, I will check the link, but as I remember we were on the step of selecting the user, which we don't have to for windows user, because there it takes the authentication from the login on the netbook.

    Christoph Kauflin
    working in Controlling, but trying to program the tools we need to do that

  • Kerberos should work for Mac OS. And Mac should be able to log on to Windows network.

    Are the Mac’s logging on to the Windows domain and getting a Kerberos ticket?

    No Macs on our network, but had setup Kerberos for SSAS and other apps. And Mac is Kerberos aware.

  • Yes, they can login in to windows shares, but it seems to be buggy, and they use Kerberos.

    The support from the IT -Department was not very enthusiastic, so I gave up. Not too many mac users ... wanted to pamper them a bit.

    Christoph Kauflin
    working in Controlling, but trying to program the tools we need to do that

  • Mac has the ability to check Kerberos tickets.

    I know on the windows side, I had policy pushed out to force Kerberos to use tcp ip rather than UDP. This for the most part increased stability as it forces acknowledgment of the packet. Latency can be a killer.

    The other issue was the ticket size - we combined ad forests, and too many groups causes an issue.

    Unfortunately I have no clue how these settings would be implemented on a Mac.

    I was fortunate in that when I set this up, my domain admin worked with me. He had the access to set up the endpoints in AD, while I could walk him through the where and what.

    You should be able to see on the sql server side success / failures of authentication from the Mac. Which is part of your troubleshooting. Saying Kerberos is setup and being used, vs. proof it is are two different things. My admin realized and any point, all I had to do was prove it was not working. Possible you have some leverage with your IT if you poke around a bit. And their lack of enthusiasm may be partly caused by lack of knowledge. Much more information out there now about setup and troubleshooting. 15 years ago, much less was out there. Back then, I remember going to several demos, where the presenter was going to set this up. It was like the ultimate in failures.

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply