Alternatives to Kerberos for passwordless server access

Question

The output from C# in VS2019 is as follows

Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll
Invalid column name 'Sequence No_(Forward)'.
Invalid column name 'Sequence No_(Backward)'.
Invalid column name 'Fixed Scrap Qty_(Accum_)'.
Invalid column name 'Scrap Factor _(Accumulated)'.

The SQL is

SELECT 
    [Sequence No_(Forward)], [Sequence No_(Backward)],
    [Fixed Scrap Qty_(Accum_)], [Scrap Factor _(Accumulated)]  
FROM 
    [AMSNeve2018CU23_Live].[dbo].[AMS Neve LIVE$Routing Line] 
WHERE 
    [Routing No_] = 'SMN812-652';

The same query works in SQL Server Management Studio:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    class Program
    {
        public static string CString;
        static void Main(string[] args)
        {
            CString = "server=NAVSERVER\\NAV;" +
                            "UID=sa;" +
                            "database=AMSNeve2018CU23_Live; " +
                            "connection timeout=30";

            string sql = "SELECT [Sequence No_(Forward)] ";
            sql += "FROM[AMSNeve2018CU23_Live].[dbo].";
            sql += [AMS Neve LIVE$Routing Line] ";
            sql += "WHERE[Routing No_] = 'SMN812-652';";
            FailMiserablyOnRead(sql);
        }
        public static void FailMiserablyOnRead(string sql)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(CString))
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            //NavRoutingLine Line = new NavRoutingLine();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }    
        }
    }
}

Solution 1

Kerberos is the best option, but you probably don't want to set it up by hand. It has a lot of moving parts and is easy to get something wrong.

Instead, you should set up a domain and join all of the computers to the domain.

You have three options for setting up a domain for this environment:

• FreeIPA. This is well supported in Linux, especially Red Hat-derived distributions, though it's also available in other distributions. This is your best choice if all or almost all of the computers run Linux; and the few Windows computers can be made to join the domain with a little work.
• Active Directory. The venerable Windows-based domain controller, which is your best choice if most of the computers run Windows.
• Both FreeIPA and Active Directory. If you have a mixed environment, you may wish to run FreeIPA to manage your Linux systems and Active Directory to manage your Windows systems, with appropriate cross-domain trusts between them.
• Samba 4 pretending to be Active Directory. You will often see this in mixed environments, or in places where someone didn't approve the budget for a Windows license to set up AD. It should be evaluated carefully as it may not support all features of modern AD functional levels.

In all cases Kerberos will be used underneath; but you don't usually have to worry about the details, as they are handled for you.

Solution 2

Kerberos is the best option for this. Is supported in nearly every distro of Linux, Windows since 2000, and Mac since 10.2. It's relatively simple to setup if you already have an existing Windows domain infrastructure. If you do, just Google the name and version of your distro and "kerberize".

Comments

[symcbean]

possible duplicate of A system for distributing SSH public keys

[Peter Smith]

What's your c# code?

[Hans Kesting]

Could you add to your question the C# code that is executing this query (and leads to this exception)? Possibly something can be changed there - but we need to see it first

[Gian Paolo]

it seems that in your SSMS query there's a space between NO_ and ( that is missing in the first query you are showing Sequence No_(Forward)

[SMor]

Generally speaking, you should not use 3 part names for database objects. You (or more likely someone else) will find it difficult to migrate your code to different environments (since a migration often involves the use of a different database name). Let the connection determine the database to use for your table references. And NO ONE should be using the sa login for anything but emergencies.

[Godfrey Small]

Unfortunately, the database is the Microsoft NAV database, and changing is NOT an option.Would c++ and ODBC hit the same snag?

[Nicholas Hunter]

I don't mean change the column names in the table, I mean change the column names in the query as I suggested in the code I provided. Sorry for the confusion. If that is not an option, please show us your C# code and maybe there's another way round the issue.

[Godfrey Small]

The column name is rejected in the same way unfortunately.

[Godfrey Small]

C++ and ODBC gives exactly the same result. The mind boggles at those who designed a database that only they can access. Well done Microsoft! Another fine mess you've got me into.

[Nicholas Hunter]

@GodfreySmall I would blame whoever came up with those column names, not Microsoft. The problem is with the c# code, not the SQL. In order to help further, we would need to see the actual code which is generating the error.

[Godfrey Small]

I have stripped the code down to something I can post here (except the password) ;-) but have not found a way to post it. Help!

[Nicholas Hunter]

Does it say "Share Edit Follow Flag" under your original question? Click "Edit".

[Godfrey Small]

Invalid column name 'Sequence No_(Forward)'. - just the same.

[Nicholas Hunter]

D'oh! The column name is incorrect. It should be "Sequence No_ (Forward)" not "Sequence No_(Forward)". I updated my reply above. Please try it again.

[Nicholas Hunter]

That wouldn't work either. The column names are incorrect.

[kaladin_storm]

I thought you said it worked in sql management studio? If it works there then just put that into your stored procedure.

[kaladin_storm]

I see that your missing a space after the underscores in your column names

[Nicholas Hunter]

In that case, please feel free to flag my answer as "accepted".