MDT2012 RC1 - Unspecified SQL Error when attempting to write to DB

Wednesday, March 21, 2012 2:23 AM

I have an MDT 2012 server setup, with MS SQL 2008 R2 (full). The MDT server is joined to a domain, and I'm using an Active Directory service account for deployment. That account is db_datareader and db_datawriter for my MDT database.

I'm attempting to execute a stored procedure to insert computer information into the database at load time. To that end, I slightly modified the stored procedure as outlined in Johan's guide at http://www.deployvista.com/Home/tabid/36/EntryID/154/language/en-US/Default.aspx

I've setup a test environment, and if I execute ZTIGather with an account that has administrative access to the SQL instance, it works fine. Executing ZTIGather as the service account does not work. The log messages from the InsertComputerName section are below:

------ Processing the [INSERTCOMPUTERNAME] section ------	ZTIGather	3/20/2012 9:54:54 PM	0 (0x0000)
Determining the INI file to use.	ZTIGather	3/20/2012 9:54:54 PM	0 (0x0000)
Using DEFAULT VALUE: Ini file = \\HW06318\DeploymentShare$\Control\CustomSettings.ini	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Finished determining the INI file to use.	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Using specified INI file = \\HW06318\DeploymentShare$\Control\CustomSettings.ini	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
CHECKING the [INSERTCOMPUTERNAME] section	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Using from [INSERTCOMPUTERNAME]: SQLServer = HW06318	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Using from [INSERTCOMPUTERNAME]: Instance = MDT	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Port key not defined in the section [INSERTCOMPUTERNAME]	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Using from [INSERTCOMPUTERNAME]: Database = MDTDB-DEV	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Using from [INSERTCOMPUTERNAME]: Netlib = DBNMPNTW	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Table key not defined in the section [INSERTCOMPUTERNAME]	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Using from [INSERTCOMPUTERNAME]: StoredProcedure = InsertComputerName	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
DBID key not defined in the section [INSERTCOMPUTERNAME]	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
DBPwd key not defined in the section [INSERTCOMPUTERNAME]	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Using from [INSERTCOMPUTERNAME]: SQLShare = DeploymentShare$	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
ParameterCondition key not defined in the section [INSERTCOMPUTERNAME]	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Default ParameterCondition 'AND' will be used for building queries with multiple parameters.	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Validating connection to \\HW06318\DeploymentShare$	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Found Existing UNC Path Z:  = \\HW06318\DeploymentShare$	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Found Existing UNC Path Z:  = \\HW06318\DeploymentShare$	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Successfully established connection using supplied credentials.	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
OPENING TRUSTED SQL CONNECTION to server HW06318.	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Connecting to SQL Server using connect string: Provider=SQLOLEDB;OLE DB Services=0;Data Source=HW06318\MDT;Initial Catalog=MDTDB-DEV;Network Library=DBNMPNTW;Integrated Security=SSPI	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Successfully opened connection to database.	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Only the first MACADDRESS value will be used in the stored procedure call.	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
About to issue SQL statement: EXECUTE InsertComputerName 'B268FC2C-4633-49B6-BB38-9BB86581E84E', '5726-4588-2554-2371-0850-1558-46', '3446-3061-6688-2363-1543-4363-41', '00:15:5D:20:33:00', 'Virtual Machine'	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
ERROR - Opening Record Set (Error Number = -2147467259) (Error Description: Unspecified error).	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
  ADO error: Unspecified error (Error #-2147467259; Source: Microsoft OLE DB Provider for SQL Server; SQL State: ; NativeError: 0)	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)
Unable to execute database query.	ZTIGather	3/20/2012 9:54:55 PM	0 (0x0000)

If I remote into the MDT server and open SQL Studio as the service account, I can successfully execute the procedure.

I've found a couple other instances of this issue floating around, but haven't found an answer as of yet. Any help would be greatly appreciated.

Oh, Firewall is off, named pipes are on

All Replies

Wednesday, March 21, 2012 11:56 AM

0

Make sure you have granted the account execute permissions on the actual stored procedure. Read and write to the database is not enough.

/ Johan
Regards / Johan Arwidmark Twitter: @jarwidmark Blog: http://www.deploymentresearch.com FB: www.facebook.com/deploymentresearch
- Reply
- Quote
Wednesday, March 21, 2012 12:29 PM

0

The account does indeed have execute granted on the procedure - looking at the permissions of the stored procedure in SQL Server Management Studio, I have the following:

The procedure being under Programmability\StoredP Procedures and being named db.InsertComputerName

Explicit:
Permission Grantor Grant
Execute dbo X

Effective:
ALTER
CONTROL
EXECUTE
REFERENCES
TAKE OWNERSHIP
VIEW DEFINITION
- Reply
- Quote
Thursday, March 22, 2012 12:06 AM

1

Well, if it works when executing ZTIGather as an admin, and not when using your deployment account, it sure appears to be a permissions related issue. However, the thing that throws me off a bit is that if you launch management studio as the deployment account it works.

SQL Profiler could help you compare the two different logons, and see if anything pops up.

/ Johan

Regards / Johan Arwidmark Twitter: @jarwidmark Blog: http://www.deploymentresearch.com FB: www.facebook.com/deploymentresearch
- Reply
- Quote
Thursday, March 22, 2012 11:15 AM

0

It has been, frustrating, to say the least. I'll see what profiler turns up, I hadn't thought of that. And maybe I can find a DBA with a few minutes on their hands.
- Reply
- Quote
Tuesday, April 24, 2012 5:06 PM

0

Sometimes priorities in IT are mercurial. I have been working on other things lately, and have just been able to get back to this issue. At this point, if I setup a test environment like Johann outlines, then log in to the workstation the test environment is setup on as the deployment account, the stored procedure executes fine.

In WinPE, I still get the same errors as outlined in the original post. Between the last time I posted and this time, I've basically rebuilt our dev environment, and set everything all back up as outlined. I'm pretty much at my wit's end with this, so if anyone has any suggestions, I'll be more than happy to entertain them. I'' have a chance to run profiler tomorrow afternoon, I'll post back with the results.
- Reply
- Quote
Wednesday, April 25, 2012 12:26 PM

0

Stream of conciousness...

Just ran SQL Profiler during ZTIGather, 1 trace while running the test environment as the database server admin, 1 while running as the MDT user from within a Windows session, and 1 running as the MDT user from within WinPE. They look pretty much identical other than the WinPE trace shows that when it gets to the "InsertComputerName" the stored procedure is never executed, whereas the trace from Windows shows that it is executed.

I'm wondering at this point if I'm missing something from WinPE, or if there is something missing on the server side (currently running MS SQL Server 2012 DEV Edition).
- Reply
- Quote
Sunday, April 29, 2012 8:22 PM

0

I have not tested accessing SQL 2012 from WinPE... Accessing SQL 2008 R2 works fine

/ Johan
Regards / Johan Arwidmark Twitter: @jarwidmark Blog: http://www.deploymentresearch.com FB: www.facebook.com/deploymentresearch
- Reply
- Quote
Friday, June 01, 2012 2:29 PM

0
Im beginning to think this problem is related to the bootimage-files to connect to the database, going to test replacing SQLOLEDB.RLL and DBNMPNTW.DLL with files from windows 7.

Edit, not what was wrong. Arwidmark came up with a solution
- Edited by Daniel B Gråhns Thursday, June 07, 2012 1:05 PM
- Reply
- Quote
Tuesday, June 05, 2012 12:08 PM

0
Im beginning to think this problem is related to the bootimage-files to connect to the database, going to test replacing SQLOLEDB.RLL and DBNMPNTW.DLL with files from windows 7.

Any updates on this one? I'm finding myself in the exact same situation but with diffrerent versions. Tried with SQL Express 2012 and 2008 R2 editions - same failure.

My sql-user is db_owner (yes I know, too much, but this is @home and test env.) on the MDT db. exactly same permissions as CE.MEDIC on the dbuser and the stored procedure.

Using: MDT: 6.0.2223.0, Server 2012, Windows ADK, (ie. WinPE 4.0), atm SQL express 2008R2Sp1 (10.50.2500),

from bdd.log:

Instance key not defined in the section [IDENTIFYCOMPUTER] ZTIGather 2012-06-05 13:49:58 0 (0x0000)
Port key not defined in the section [IDENTIFYCOMPUTER] ZTIGather 2012-06-05 13:49:58 0 (0x0000)
Using from [IDENTIFYCOMPUTER]: Database = MDT ZTIGather 2012-06-05 13:49:58 0 (0x0000)
Using from [IDENTIFYCOMPUTER]: Netlib = DBMSSOCN ZTIGather 2012-06-05 13:49:58 0 (0x0000)
Table key not defined in the section [IDENTIFYCOMPUTER] ZTIGather 2012-06-05 13:49:58 0 (0x0000)
Using from [IDENTIFYCOMPUTER]: StoredProcedure = IdentifyComputer ZTIGather 2012-06-05 13:49:58 0 (0x0000)
Using from [IDENTIFYCOMPUTER]: DBID = mdtdbusr ZTIGather 2012-06-05 13:49:58 0 (0x0000)
Using from [IDENTIFYCOMPUTER]: DBPwd = ******** ZTIGather 2012-06-05 13:49:58 0 (0x0000)
SQLShare key not defined in the section [IDENTIFYCOMPUTER] ZTIGather 2012-06-05 13:49:58 0 (0x0000)
ParameterCondition key not defined in the section [IDENTIFYCOMPUTER] ZTIGather 2012-06-05 13:49:59 0 (0x0000)
Default ParameterCondition 'AND' will be used for building queries with multiple parameters. ZTIGather 2012-06-05 13:49:59 0 (0x0000)
OPENING STANDARD SECURITY SQL CONNECTION to server buran.crc.local using login mdtdbusr. ZTIGather 2012-06-05 13:49:59 0 (0x0000)
<Message containing password has been suppressed> ZTIGather 2012-06-05 13:49:59 0 (0x0000)
Successfully opened connection to database. ZTIGather 2012-06-05 13:49:59 0 (0x0000)
Only the first MACADDRESS value will be used in the stored procedure call. ZTIGather 2012-06-05 13:49:59 0 (0x0000)
About to issue SQL statement: EXECUTE IdentifyComputer '00:24:E8:B6:DE:71' ZTIGather 2012-06-05 13:49:59 0 (0x0000)
ERROR - Opening Record Set (Error Number = -2147467259) (Error Description: Unspecified error). ZTIGather 2012-06-05 13:49:59 0 (0x0000)
ADO error: Unspecified error (Error #-2147467259; Source: Microsoft OLE DB Provider for SQL Server; SQL State: ; NativeError: 0) ZTIGather 2012-06-05 13:49:59 0 (0x0000)
Unable to execute database query. ZTIGather 2012-06-05 13:49:59 0 (0x0000)

and later:

Instance key not defined in the section [CSETTINGS] ZTIGather 2012-06-05 13:49:59 0 (0x0000)
Port key not defined in the section [CSETTINGS] ZTIGather 2012-06-05 13:49:59 0 (0x0000)
Using from [CSETTINGS]: Database = MDT ZTIGather 2012-06-05 13:49:59 0 (0x0000)
Using from [CSETTINGS]: Netlib = DBMSSOCN ZTIGather 2012-06-05 13:49:59 0 (0x0000)
Using from [CSETTINGS]: Table = ComputerSettings ZTIGather 2012-06-05 13:49:59 0 (0x0000)
StoredProcedure key not defined in the section [CSETTINGS] ZTIGather 2012-06-05 13:49:59 0 (0x0000)
Using from [CSETTINGS]: DBID = mdtdbusr ZTIGather 2012-06-05 13:49:59 0 (0x0000)
Using from [CSETTINGS]: DBPwd = ******** ZTIGather 2012-06-05 13:49:59 0 (0x0000)
SQLShare key not defined in the section [CSETTINGS] ZTIGather 2012-06-05 13:49:59 0 (0x0000)
Using from [CSETTINGS]: ParameterCondition = OR ZTIGather 2012-06-05 13:49:59 0 (0x0000)
OPENING STANDARD SECURITY SQL CONNECTION to server buran.crc.local using login mdtdbusr. ZTIGather 2012-06-05 13:49:59 0 (0x0000)
<Message containing password has been suppressed> ZTIGather 2012-06-05 13:49:59 0 (0x0000)
Successfully opened connection to database. ZTIGather 2012-06-05 13:49:59 0 (0x0000)
About to issue SQL statement: SELECT * FROM ComputerSettings WHERE UUID = '4C4C4544-0037-3910-8053-C4C04F4D324A' OR SERIALNUMBER = 'D79SM2J' OR MACADDRESS IN ('00:24:E8:B6:DE:71') ZTIGather 2012-06-05 13:49:59 0 (0x0000)
Successfully queried the database. ZTIGather 2012-06-05 13:49:59 0 (0x0000)
Record count from SQL = 0. ZTIGather 2012-06-05 13:49:59 0 (0x0000)

Running Mgmt studio as the sql user mdtdbusr:

USE [MDT]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[IdentifyComputer]
@MacAddress = N'00:00:00:00:00:01'
SELECT 'Return Value' = @return_value
GO

Gives

ID Description AssetTag UUID SerialNumber MacAddress
2 NULL NULL NULL NULL 00:00:00:00:00:01

and

Return Value
0

and in settings:

The entry is created (To huge to copy paste)

Running:

USE [MDT]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[IdentifyComputer]
@MacAddress = N'00:00:00:00:00:02'
SELECT 'Return Value' = @return_value
GO

Creates an entry with ID 3 (as it should)

But for some stupid reason it can't do it from the PE 4.0 environment... And just the stored procedure.?!

Going for standard 2012 installation to get profiling up.....

/Christian

Update:

Got SQL 2012 std up and running with profiling. And sadly, I'm not good enough to say whats wrong. I can state that the procedure is run if using mgmt studio - no problemo. But run from MDT it hits the server but does not seem to be actually executed..

Got any pointers 4 me?

/Christian
- Edited by Christian Riiser Tuesday, June 05, 2012 8:01 PM Continuation
- Reply
- Quote
Tuesday, June 05, 2012 8:56 PM

2
Hey Christian

This is a bug in the boot image... contact me offline (you have my email), and I can provide you with a workaround I found after troubleshooting this issue for another customer...

I will post a full article once I get a reply from the MDT team about my findings.

/ Johan
Regards / Johan Arwidmark Twitter: @jarwidmark Blog: http://www.deploymentresearch.com FB: www.facebook.com/deploymentresearch
- Proposed As Answer by Christian Riiser Wednesday, June 06, 2012 8:09 PM
- Reply
- Quote
Thursday, June 07, 2012 12:33 PM

1

ANd now he has - Bug in MDT 2012 in conjunction with ADK -

Solution. -> http://www.deploymentresearch.com/Blog/tabid/62/EntryId/62/Cannot-call-SQL-Stored-Procedures-from-MDT-2012-generated-WinPE-4-0-boot-image.aspx

/Christian
- Reply
- Quote

MDT2012 RC1 - Unspecified SQL Error when attempting to write to DB

All Replies

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support by product

Other support links

Not an IT pro?