OSIsoft: Link to External Table & SQL Security. PI AF v2010

Okay, so let's move on to the next demo. I'll go ahead and
check this in. And, let's create
another new Table, except this one,
this time we're going to do a Link. So instead of calling this Table
From SQL, let's call this Table
In SQL.
Or maybe even just to make it real obvious, Table
Still In SQL. What we're saying, what the point I'm trying
to make here is that this is data that's going to be linked
to a SQL Server Table that's
not going to be affected by whatever I do over here with it.
I've just got a Read Only access to this. So,
Data From SQL is the Data that, hey, I, I got it in
here, now I can do anything I want with it. This is more of a link,
or this is a link, so, in this case, I'm simply
going to be retrieving it. Now the beauty of this is that it's only
being retrieved when it's required by an AF Client. So it's
only when an AF Client's going to be looking at some of the
Attributes that make use of this Table, that will actually
involve the link to the SQL Table. And, in
our subsequent videos we're going to take a look at how we would actually make use
of this Tabular Data as a
Val...the Attributes that we show in an
Element, so we'll, we'll make use of this in just a few minutes.
But, right now let's just concentrate on getting this Data in,
and we'll concentrate on using it later on. So, to
bring this in as a Linked Table, I'll choose this
Link Option, and as you can see,
we're, we've actually picked up some of the
credentials that we used before.
That's the only kind of -- it's, it's been, kind of cached
locally since we just recently
made this retrieval. So, if you're
doing this for the first time, you're very likely not to see that.
So, in fact, why don't we do this? Let me check this in.
I don't want to do any cheating here by having
some nice credentials all cached waiting for us. I just
exited out of the PI System Explorer and I'm
going to load it back up again. We should not see that
cached data
now when I choose Link. Let's choose Link.
As you can see this is more likely what you would
typically see. You know, there's no cached credentials
already sitting there waiting for us to exploit. So,
you know, here's, here's the previous Table we did. Now let's do
the new one that's going to be a Link. For a Link,
we'll do that same retrieval of the Connection
String that we did before. I'll specify Only To Be Provided
For SQL. I'll go ahead and choose the
right one.
That's the right instance of SQL. I'm going to choose
NT Integration and we'll choose the database.
That's Material ID, Test
Connection. That looks good.
Okay. Now, how do we
specify the Query? Well, it's the same Query that we did before.
So, there's, there's really nothing new here. Actually,
let's make this a little bit more interesting. I'll add a couple
more. Let's add Viscosity and Melting Temperature.

Density, Viscosity,
and in double quotes
let's look at "melting temperature"
from Material Properties.
And we're just about ready to finish,
except we've got this one last hurdle here, and
boy it is a big hurdle - Security. You know what?
We have written so much
about this issue, or how to set this up and what your choices
are, that I don't even want to attempt to do it
in this. I mean this is, this is one of those times where,
in this class,
and typically when you come to one of our regular classes we
simply do not go into the detail required
to go over all the different permutations here,
because it's an enormous topic.
We don't do it justice in our classes or in
the CBT's because, to be honest, this is one of those things
you only do it once, generally, you know, when you first
configure the system, and if you're, if you're
a SQL professional, you know, an IT
professional that's been working with SQL, you certainly don't need to hear what we're going to say
in a, in a short class on this topic.
And, if you're not, this is something that we can easily
help you with on Technical Support. We've got it
documented fairly well. We've also got, in addition to the documentation,
we've got hundreds of Technical Support
calls from people with all different kinds of configurations,
some using Kerberos for security,
some using, you know, going across domains, you know,
some using different versions
of Windows. You know, you can imagine the permutations are huge, so
we don't attempt to try to teach it here. Let me actually show
you what we can show you that we think is useful
in the context of a user class.
So, let's go ahead, and I'll go ahead and acknowledge.
And, what we should see now is --
there we go. Our Retrieval's working. It brought in those Values that
we needed before. And, if I go ahead and
check this in, you know, what you'll notice now
it's, it's the same data. The only difference,
of course, is the data that we see right here,
this is data that we've copied into AF. This is data that's linked.
And, this is going to be something that's a Read Only, so
as you can see, I cannot go in,
I cannot double-click on this and start editing,
which is something I could easily do -- if you remember I could
easily do that over here because that's my own local
copy. So the issue with Security is
really one of how you're going to be making this
connection. If you recall, when we did this,
this Link here, we had three
different Options. And that's what I'd like to address
here. What, what do these three different Options mean?
And where you would find more information about these. So,
when we specify no additional security
context, which is the one that seems
to be most prominently mentioned throughout our documentation,
and I assume is the one that most of you will be using, then
what this simply means is that we're going to use the Regular
Credentials of the AF Server to connect to
SQL Server in this case, and retrieve that Data from the
Linked Table. Now, Impersonate Client
is an Option that will allow the,
the AF Client that's running, is actually going to,
you know, that we were going to get the Credentials from that AF Client,
the AF Server will be connecting to SQL Server, but
impersonating that AF Client. And then,
Supply Password, we don't specifically put it here, but
this is what we're going to be passing as
if you're using SQL Server Credentials. So, if you decide to use
SQL Server Credentials, you're going to be using this Option right here.
Well, where would you find out more information about
all of these? Thankfully, we've got a very good
Section in the AF User's Guide that
explains exactly what you're going to be
expected to do on the Server side if
you're choosing any of those three. So, it's the
Section called Importing or Linking. It's currently on Page 204 in
the User's Guide. And, you'll notice that we define those
three, or we make reference to those three different instances here.
So, we say, When linking if you're using
Integrated Security with "No additional security context" that was
the first Option. Then we specify some things
to do. If you're using SQL Server authentication,
then we specify some other things.
If we're choosing Impersonation, there's some other things. All
of these things are referenced in great detail
if you look at this Section here on,
you know, on making this Link.
So, for example, there are three things to
look for here. If you're using the Integrated Security
and if you're specifying the
"Impersonate Client" Option, then here's the
specifics that we're suggesting that you do.
There's a Section of Page 208 on Configuring
Security in the Target Database, and then actually doing the linkings
described on 210. If you're using
the, the Integrated Security
and you've got the "no additional security option," then you need
to do it this way. And, as you go through this, you'll see,
, here's the third one. If you're using SQL Server Security
then we suggest you go about it this way.
Now, a second place you can go is
there's a Section that beings on Page
304 in that same document, and you'll find
all the security considerations to
keep in mind as you're configuring this.
And, as you can see, there are a wealth of recommendations here
on how to make sure that your Security Configuration is
truly secure. Okay, so in summary, when you're
trying to either Import or Link
to a Table that's in a Relational Database that's
OLE DB compliant, you know, when it comes to the
actual configuration
we do suggest that you get involved with
whoever your local SQL experts are so that you
can, you know, make the right Connections, make the right Queries,
and more importantly, have the right Security
Settings that are going to work in your environment. And, if you need help with
the Security Configuration, please take a look in
our AF User's Guide. There's a Section on Importing
or Linking SQL Server Data. Also, look at
the Additional Recommendations we have for
Server Security, and if you still need help, please contact our
Technical Support Group.