Knowledge Article
How to setup a trusted connection user in SQL Server 2005 or 2008.
How to setup a trusted connection user in SQL Server 2005 or 2008.
BMC Capacity Management for Mainframes
Capacity Management Database
What is the correct procedure to set up a SQL Server trusted connection (Windows Authenticated) user in SQL Server 2005 or 2008?
Setting up a trusted connection user in SQL Server 2005 or 2008 is a little different than in previous releases of SQL Server. If the user is not set up correctly you may see an error similar to the following in the Automator trace file when the initial populate is attempted: CREATE TABLE ATONET\UB6SS.CAXINTVL (Int_start_date CHAR(8),Int_start_time CHAR(5),Int_duration FLOAT,INTTYPE CHAR(1),Int_descr CHAR(8),INTSUBT CHAR(1),INDEXX INTEGER,Label CHAR(8),CHANGED CHAR(1),ALERT CHAR(1),RELEASE CHAR(8))
ERROR: SQLExecDirect in CreateTable failed. SQLState: 37000 - SQLError: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '\'. The problem is that the \ that is included in the network id (ATONET\UB6SS) is invalid syntax. Visualizer (version 4.2.05)
Visualizer (version 4.2.04)
Visualizer (version 4.2.03)
Visualizer (version 4.2.02)
Visualizer for Unix/Windows; Microsoft SQL-Server; |
Legacy ID:KA286766 Step 1: Setup a Windows Authenticated login in SQL Server.
Step 2: Make sure the user has the dbcreator role.
Step 3: The user should show up under the Security branch.
Step 4: Create the new database. Click on the Owner browse button and select the loginid you created above.
Step 5: Select the space for the database. Click OK to create the database.
Step 6: Under Databases\{your database} expand Security and then Users. Double click on dbo to open the properties box. Make sure your login name is there and the Default schema is dbo. Make sure db_owner is checked for both Owned Schemas and Role Members.
Step 7: Check the dbo schema under Databases\{your database}\Security\Schemas.
Step 8: Open up Automator and select File\New to create a new script. Select Edit\Add Target Database/Group and select new to create a new DSN. On the Create New Data Source window select the SQL Native Client and click Next.
Step 9: Type in the DSN name and select the SQL Server.
Step 10: Select the With Intergrated Windows authentication option.
Step 11: Select your database from the pull down list and click Next. Test the conection and click OK.
Step 12: Click OK on the Automator catalog window.
Step 13: The script looks like this.
Summary of Run: -Running script c:\cases\bmccase\trusted2.b1a 8/29/2008 3:01 PM ... Automator : 0 error(s), 0 warning(s) Automator : Run script finished at 8/29/2008 3:01 PM Full Log: Running script c:\cases\bmccase\trusted2.b1a 8/29/2008 3:01 PM ... Target Database : Running events for target database: Trusted2... Populate : Running Populate Event (8/29/2008 3:01:15 PM) on Trusted2... Populate : Populate all values Populate : 1 (All) input matching input files were found Populate : 1 DistSys Measurements input matching input files were found Populate : Creating data source DistSys Measurements data from input file: C:\Cases\bmccase\L3Super226X.vis... at 15:01:15 Populate : Bulk utility is in effect Populate : Population of a table CAXINTVL at record 6 finished at 15:01:16 Populate : Population of a table CAXCMDS at record 43 finished at 15:01:16 Populate : Population of a table CAXNODES at record 46 finished at 15:01:16 Populate : Population of a table CAXRGDS at record 50 finished at 15:01:16 Populate : Population of a table CAXFILES at record 52 finished at 15:01:16 Populate : Population of a table CAXWRESS at record 62 finished at 15:01:16 -clip- Populate : Population of a table CAXPRCD at record 1228 finished at 15:01:19 Populate : Population of a table CAXNODED at record 1235 finished at 15:01:19 Populate : Population of a table CAXNTCPD at record 1240 finished at 15:01:19 Populate : Population of a table CAXNTNOD at record 1244 finished at 15:01:20 Populate : Bulk utility command line is "Trusted2.dbo.CAXPROCD" in C:\DOCUME~1\jespicer\LOCALS~1\Temp\TRUSTE~1.271\VIS6.tmp\CAXPROCD.OUT -eC:\DOCUME~1\jespicer\LOCALS~1\Temp\TRUSTE~1.271\VIS6.tmp\CAXPROCD.ERR -m 666 -b 1000 -c -q -oC:\DOCUME~1\jespicer\LOCALS~1\Temp\TRUSTE~1.271\VIS6.tmp\CAXPROCD.log -t, -T -SVMSUPPORT2. Populate : Population of 120 rows into CAXPROCD table via bulk utility started at 15:01:20. Populate : Population of table CAXPROCD via bulk utility finished at 15:01:21. Populate : Building indexes of table CAXPROCD started at 15:01:21 Populate : Bulk utility command line is "Trusted2.dbo.CAXWCATD" in C:\DOCUME~1\jespicer\LOCALS~1\Temp\TRUSTE~1.271\VIS6.tmp\CAXWCATD.OUT -eC:\DOCUME~1\jespicer\LOCALS~1\Temp\TRUSTE~1.271\VIS6.tmp\CAXWCATD.ERR -m 666 -b 1000 -c -q -oC:\DOCUME~1\jespicer\LOCALS~1\Temp\TRUSTE~1.271\VIS6.tmp\CAXWCATD.log -t, -T -SVMSUPPORT2. Populate : Population of 23 rows into CAXWCATD table via bulk utility started at 15:01:21. Populate : Population of table CAXWCATD via bulk utility finished at 15:01:21. Populate : Building indexes of table CAXWCATD started at 15:01:21 Populate : Bulk utility command line is "Trusted2.dbo.CAXCTRLD" in C:\DOCUME~1\jespicer\LOCALS~1\Temp\TRUSTE~1.271\VIS6.tmp\CAXCTRLD.OUT -eC:\DOCUME~1\jespicer\LOCALS~1\Temp\TRUSTE~1.271\VIS6.tmp\CAXCTRLD.ERR -m 666 -b 1000 -c -q -oC:\DOCUME~1\jespicer\LOCALS~1\Temp\TRUSTE~1.271\VIS6.tmp\CAXCTRLD.log -t, -T -SVMSUPPORT2. Populate : Population of 12 rows into CAXCTRLD table via bulk utility started at 15:01:21. Populate : Population of table CAXCTRLD via bulk utility finished at 15:01:21. Populate : Building indexes of table CAXCTRLD started at 15:01:21 -clip- Populate : Bulk utility command line is "Trusted2.dbo.CAXPROSD" in C:\DOCUME~1\jespicer\LOCALS~1\Temp\TRUSTE~1.271\VIS6.tmp\CAXPROSD.OUT -eC:\DOCUME~1\jespicer\LOCALS~1\Temp\TRUSTE~1.271\VIS6.tmp\CAXPROSD.ERR -m 666 -b 1000 -c -q -oC:\DOCUME~1\jespicer\LOCALS~1\Temp\TRUSTE~1.271\VIS6.tmp\CAXPROSD.log -t, -T -SVMSUPPORT2. Populate : Population of 16 rows into CAXPROSD table via bulk utility started at 15:01:30. Populate : Population of table CAXPROSD via bulk utility finished at 15:01:31. Populate : Building indexes of table CAXPROSD started at 15:01:31 Populate : Bulk utility command line is "Trusted2.dbo.CAXRDIRD" in C:\DOCUME~1\jespicer\LOCALS~1\Temp\TRUSTE~1.271\VIS6.tmp\CAXRDIRD.OUT -eC:\DOCUME~1\jespicer\LOCALS~1\Temp\TRUSTE~1.271\VIS6.tmp\CAXRDIRD.ERR -m 666 -b 1000 -c -q -oC:\DOCUME~1\jespicer\LOCALS~1\Temp\TRUSTE~1.271\VIS6.tmp\CAXRDIRD.log -t, -T -SVMSUPPORT2. Populate : Population of 4 rows into CAXRDIRD table via bulk utility started at 15:01:31. Populate : Population of table CAXRDIRD via bulk utility finished at 15:01:31. Populate : Building indexes of table CAXRDIRD started at 15:01:31 Populate : Validation of execution of bulk utility for table CAXPROCD started at 15:01:31 Populate : Validation of execution of bulk utility for table CAXPROCD finished at 15:01:31 Populate : Validation of execution of bulk utility for table CAXWCATD started at 15:01:31 Populate : Validation of execution of bulk utility for table CAXWCATD finished at 15:01:31 -clip- Populate : Validation of execution of bulk utility for table CAXPROSD finished at 15:01:33 Populate : Validation of execution of bulk utility for table CAXRDIRD started at 15:01:33 Populate : Validation of execution of bulk utility for table CAXRDIRD finished at 15:01:33 Populate : 0 MVS/VM Predictions input matching input files were found Populate : Event finished at 8/29/2008 3:01:33 PM. Automator : 0 error(s), 0 warning(s) Automator : Run script finished at 8/29/2008 3:01 PM |
Attachment(s):