AS400 login

The ability import data into SQL Server has nothing to do with iSeries credientials.  That depends on the SQL Server security.  On the iSeries side you just need *USE rights to the tables in question (and QSECOFR has that and more).

If this is a production system, then handing out QSECOFR credentials like this is a -terrible- security practice, and an invitation to disaster.  This profile had virtually unlimited access to every object and function in the system and is enormous overkill when what it sounds like this process needs is read-only access to certain iSeries tables.

Using the QSECOFR profile, this user has the ability to alter the contents of any table in the system, clear table, delete tables, alter the layout of table (which could break other applications), and all kinds of bad things.  This profile can be used to create other administrator-level profiles, access and delete security auditing details, and a long, long list of other privileged actions that should be strictly limited and monitored.

If you don't already have a user ID or group set up with *USE rights to the tables in question, then you should probably create a group profile with appropriate data rights, and then create an AS/400 user ID for this process, and make it a member of that group.  

- Gary Patterson

Any way I can check to see if my QSECOFR username has USE rights table?  I know you said QSECOFR does but it would be nice to confirm.  Also here is the information that I had to give to them for access.  

I am kind of needing to know if QGPL is where the package collection would be.  This is a question they had asked me.  

Here is a screenshot of the error they are getting about permissions on the sql side of things.

Could their be a possibility that my QSECOFR is not a full security officer username?  How can I check that or create a new one with all access and authority?
3.png

First, you cannot remove any rights from QSECOFR, so there's no point in checking. Second, QSECOFR should definitely not be used at all, much less as a basic remote connection user. Third, QSECOFR can indeed be restricted from use for some connection functions related to this (but it's not clear yet what connections are being used). Fourth, even if fundamental native elements are not restricting QSECOFR, there may be non-native objects (e.g., exit programming) that reject QSECOFR (as they should).

But none of that really addresses your problem.

...the credentials I have given him on the iseries side do not allow import.

Can you describe what is meant by "import"? DB2 on iSeries has an 'import' function that is different from DB2 on other platforms. A more detailed description would be helpful.

Tom

All I know is that we are converting to a new software that is SQL based.  I downloaded and installed SQL developer's edition so this could take place. It was needed for some reason for the conversion.  The company has created some sort of an SQL software that links from the iseries to the SQL database.  They need to import data from my old accounting system which is on the iseries into the SQL based accounting system.  They cannot do this at this time because their software keeps telling them that the QSECOFR username does not have the right credentials.   Not sure how else to explain it.

Let's clarify, just to make sure we are on the same page:  

SQL is a language, not a product.  When you say "SQL developer's edition" do you mean "Microsoft SQL Server Developer's Edition"?

Assuming that is the case, there are TWO database systems involved here:  

1) The AS/400 DB2 database system, for which the QSECOFR credentials you have are more than adequate to access ANY data in the system, provided the connection from the SQL Server box to the AS/400 is properly configured and all of the supporting services are running. (Unless the QSECOFR profile no longer has *ALLOBJ rights, which would be very unusual).  You can use the DSPUSRPRF QSECOFR command to verify that the QSECOFR profile has *ALLOBJ special authority.  If it does, then AS/400 authority just simply isn't the issue.

2) The Microsoft SQL Server database system, to which the user "QSECOFR" is probably meaningless.  If you are getting an authority error, it is much more likely that the problem is on the SQL Server side of the equation.  The vendor will need to log onto SQL Server using valid windows or SQL Server credentials, depending on how SQL Server was set up.

I work with MS SQL Server based applications that access AS400 data almost every day, and I'm happy to help you, but it would be helpful if you could provide specific troubleshooting information.  "Some sort of software" and "does not have the right credentials" is a little too vague to use to diagnose the problem, I'm afraid.

Please provide some specifics:

1) What is the EXACT error message that the vendor's tool is throwing?
2) What version of MS SQL Server is being used?
3) How EXACTLY is the connection from the SQL Server box to the AS400 configured?  Is it a linked server?  Is it an ODBC connection, and OLEDB connection or ADO.NET connection from a Windows program?  Please provide specific connection details, including the connection parameters, DSN information, or connection string.  If they are using a program to attach, provide the failing line of code.

Once we figure out what mechanism is used to connect from SQL Server to AS400 DB2, we can provide detailed troubleshooting steps.

- Gary Patterson

I'm a round of posts behind in my last post, so give me a few minutes to catch up.

OK, from the screen snap, they are just using the SQL Server Import and Export Wizard.  Please confirm that we are trying to move data FROM the AS/400 TO SQL server.

What specific table(s) are they trying to access on your AS400?

The people doing the conversion for me should know of the things stated above.  I will get with them and have them look at some of these responses and see if it helps out or not.  I did check QSECOFR and it does have *ALLOBJ

Not sure what else to tell them.  They say that the username I am giving them is not going to work.

...some sort of an SQL software that links from the iseries to the SQL database.

Yes, re-reading from the beginning makes a difference. I was blinded into thinking of it as a "connection to AS/400" problem. Reading it over with a different mindset makes things look very different.

Tom

@Tom: For obvious reasons, I've never tried to alter QSECOFR special authorities.  Interesting to know that you can't.  

@mkramer777:  Regarding the "default schema" question:  QGPL may or may not be is fine.  In an AS/400 DB2 connection, table names can be qualified with a library name (called a schema in SQLese), or unqualified.  Objects using qualified table names are easy to resolve, since the libary(schema) and table are both specified:

Qualified:

select col1, col2, col3 from myschema.mytable

Unqualified:

select col1, col2, col3 from mytable

Qualified object names are easy for DB2 to deal with, since both the library and table name re supplied right in the query.

Unqualified objects, on the other hand, are more complicated for DB2 to find, and there are multiple connection properties that control how and where the system attempts to find unqualfied objects.  One option is to search a default schema/library, and if the connection is configured to do that, and queries don't fully qualify object names, then it is important to have the correct default schema name set up.  

So, if the tables they want to import from the AS/400 to SQL Server happen to reside in QGPL, then setting this schema as the default might be useful, assuming the connection is set to search the default schema when an unqualified object name is encountered in an sql statement.  If not, then it is probably meaningless.  

Hope this is helping.

- Gary Patterson

This is what they told me I needed to do.  


"I talked to some folks back here and The only other thing we can try is to map a network drive to the iSeries"


Can you tell me how to do this?

No idea what that has to do with running an SQL query against the database, but, sure, you can map a network drive to the AS400.

You'll need AS/400 NetServer configured and running (which allows you to share AS400 IFS folders in a Windows network using CIFS) , and you'll need to configure the desired shares in Netserver.

One Netserver is running, your AS400 will look like a Windows file server to Windows clients, and you can just map a drive letter like you would to any other share, using the NET USE command, or browsing to it in Windows Explorer and use the Map Network Drive tool.
 
Netserver configuration:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Frzaha%2Fdkmap.htm

Mapping a drive:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Frzaha%2Fdkmap.htm

- Gary Patterson

Is netserver part of client access that is already installed on my machine.  I use navigator a bit by not netserver.

Question for Gary

Do you publish some sort of an as400 information and programming mailing?  I could swear I have heard of your name before.

The thing they are trying to do here - namely extract data from the AS400 to SQL server - is pretty easy, and something that an experienced consultant can accomplish in most environments in a very short period of time.  

It is not a big deal, and is not very complicated, though there can certainly be complexities depending on your system configuration, security environment, and network environment.  

I'd be surprised if an experienced consultant couldn't help you though this in less than an hour.

- Gary Patterson

Check out my EE profile: https://www.experts-exchange.com/M_4382324.html

Netserver is part of the AS400 operating system, not the Navigator client.  NetServer administration is mostly done though Navigator, though.  

All of the instructions for setting up Netserver are covered in the link I posted.

As far as hearing my name before: I've worked as an AS/400 consultant, trainer, writer, and technical editor for over 20 years, and have a pretty broad client base, so it is certainly possible our paths have crossed before.  I answer a lot of AS400 questions here at EE, and occasionally at other sites, too.
 
- Gary Patterson

I went to start run \\name of server and the folders that show up in there are:

QDIRSRV
QUMB
QCA400
QIBM

They all say share below them.  Should there be more?  If the tech guys I am working with needed to get a connection to QGPL does it need to show up with these others?  Sorry for all the questions.  I'm trying to help these so-called experts with my limited knowledge.

you may try this comprehensive article
http://geekswithblogs.net/13DaysaWeek/archive/2010/05/16/connecting-to-db2-from-ssis.aspx

Or this - try to edit the connection string and embed the user/password there.
http://www.lajak.com/blog/?p=511